推荐先阅读
王佩丰 excel 学习笔记 了解数据类型
日期的实质是一个数字,1.00代表的是1900年1月1日00:00
整数代表一天
小数代表时刻
只是把数字类型改了一下而已。
小数代表具体的时刻,例如0.5就代表12:00,把一天平分正好是12:00,也就是1/2(0.5)既然时间背后其实就是数字,那么时间也就可以进行运算。
整数转化为分钟
我们已经知道了1代表一天,那么一小时就是1/24,一分钟就是1/24/60。
正如这个表显示,开始于9:00工作90分钟,就是9:00+90分钟,但90并不是时间格式,直接更改数据类型它也会变成一个日期,所以需要我们进行换算。对90除24再除60,它就变成了时间格式中的分钟所代表的数字了。
这个计算同理,只需要再最后把数字类型改为常规。
日期代表整数所以可以直接算,如果结果显示为日期,需要把数字类型改为常规。
推算日期
Date是一个算日期的函数
Year算年份month算月份day算日
=DATE(YEAR(B5),MONTH(B5)+C5,DAY(B5))
我们要的是B5单元格的年日不变就是月份加4个月,所以就用date函数,注意月份加4时,在括号外边加。
算本月的最后一天是几号
本月的最后一天就是下个月的第一天的前一天,所以我们只要在date函数里面月份加1日期写成1,再在函数的括号外-1.
=DATE(YEAR(B13),MONTH(B13)+1,DAY(1))-1
同时这个函数可以简化,把day写成0
=DATE(YEAR(B13),MONTH(B13)+1,0)
求本月有几天
本月有几天就是本月最后一日是几号,所以我们把上面的公式嵌套在day函数里就行了。
=DAY(DATE(YEAR(B21),MONTH(B21)+1,0))
最后把数字类型改为常规就可以了。
日期间隔计算(excel隐藏函数)
=DATEDIF (开始日期,结束日期,比较尺度)
计算在职时间
=DATEDIF(B5,C5,"y")
这个函数的意思时算时间间隔以年为尺度,开始于2002/9/10结束于2012/9/9日,并且这个函数计算时是按满周年才算,而实际工作中,他干到9月9日下班也是干了十年,所以我们要给结束日期加1.
这样就满足了日常工作的需求了 。为什么不自己日期相减再除以365呢?因为这样算不精确,会受到闰年的影响。
这个函数的第三参数有六种写法
y 年份 m 月份 d 天数
ym 在年的基础上看月份,例如上面第一个9年11月,用y做参数只能显示9(年),而用m显示为119(月),而用ym作为参数就是11(月)
md 在月的基础上看天数
yd 在年的基础上看天数
利用第三参数不同的写法,就可以算出在职时长了
=DATEDIF(B13,C13,"y")&"年"&DATEDIF(B13,C13,"ym")&"月"&DATEDIF(B13,C13,"md")&"天"
这个就是把三个datedif函数用连字符写出来,y代表年,ym代表在年的基础上的月,md代表在月基础上的天数。这样就得到一个完整的在职时长了。
星期计算
Weeknum函数,计算日期在所在年的第几周
=WEEKNUM(B3,1)
它的第二参数,如果是1就代表周日为每周的第一天,如果是2就代表周一为每周的第一天。
Weekday函数算周几
=WEEKDAY(B8,1)
第二参数同上,都有很多规则,excel会提示。
把两个函数连接
="第"&WEEKNUM(B13,2)&"周的"&"第"&WEEKDAY(B13,2)&"天"
函数只返回数字,所以我们用连字符把文字和函数连接起来,得到人类看的懂的样式。
Text函数改格式
=TEXT(B3,"aaaa")
Text函数用来更改文本的格式,”aaaa”在自定义格式中代表星期显示。
Text也可以把文本改为日期(数值)
先把这个日期的文本改为0000-00-00的显示状态,而文本通过*1的方式可以变为数值。
这样就把一个假的日期改为一个真正的日期了,只需要在数据类型里改为短日期或长日期。