快捷搜索:  汽车  科技

excel中关于日期的函数怎么用?这两个关于Excel日期函数的经典案例

excel中关于日期的函数怎么用?这两个关于Excel日期函数的经典案例A列是日期,部分内容截图如图3-12-8所示。根据日期快速计算归属的季度,分享8种方法。


excel中关于日期的函数怎么用?这两个关于Excel日期函数的经典案例(1)

这两个关于Excel日期函数的经典案例,一般人都不会,但超级实用!




excel中关于日期的函数怎么用?这两个关于Excel日期函数的经典案例(2)

  1. 根据日期计算季度的8种方法!
  2. 如何根据日期判断闰年还是平年?


excel中关于日期的函数怎么用?这两个关于Excel日期函数的经典案例(3)




根据日期计算季度(8种方法)

根据日期快速计算归属的季度,分享8种方法。

A列是日期,部分内容截图如图3-12-8所示。


excel中关于日期的函数怎么用?这两个关于Excel日期函数的经典案例(4)

图3-12-8

方法一:INT函数

B1公式=INT((MONTH(A1) 2)/3),双击B1单元格右下角黑色 可以自动填充其他单元格公式。

公式解释说明:

先用MONTH函数计算日期对应的月份,再用INT函数求季度,INT函数功能是将数字向下舍入到最接近的整数。

比如,如果月份是12月,(12 2)/3=4.67 INT(4.67)=4。

方法二:ROUNDUP函数

B1公式=ROUNDUP(MONTH(A1)/3 0)

公式解释说明:

ROUNDUP函数功能是向上舍入,即将数字朝远离0的方向舍入。

比如ROUNDUP(1/3 0)=1。

和这个函数功能相反的函数是ROUNDDOWN ROUNDDOWN(1/3 0)=0。

方法三:CEILING函数

B1公式=CEILING(MONTH(A2) 3)/3

公式解释说明:

CEILING函数功能是将数字向上舍入为最接近的整数,和ROUNDUP不同的是,ROUNDUP是按照小数位数取舍,CEILING是按照指定基数的整数倍取舍。

比如,CEILING(4 3)结果是6,就是按照3的整数倍向上取整,所以结果是6。

方法四:FLOOR函数

B1公式=FLOOR((MONTH(A1) 2)/3 1)

公式解释说明:

FLOOR函数功能是将数字向下舍入为最接近的整数,和ROUNDDOWN不同的是,ROUNDDOWN是按照小数位数取舍,FLOOR是按照指定基数的整数倍取舍。

比如,FLOOR(4.67 1)结果是4。

方法五:LEN函数

B1公式=LEN(2^MONTH(A1))

公式解释说明:

LEN函数是求文本的长度,如果月份是12月,2^12=4096 LEN(4096)=4。如果是8月,2^8=256 LEN(256)=3。

方法六:只用MONTH函数

B1公式=MONTH(MONTH(A1)*10)

公式解释说明:

月份*10得到结果是2位数或3位数,Excel默认的是1900年的日期系统,如果月份是12,那120对应的日期是1900年4月29日,再对这个日期求月份就是4。

我们可以看看分步计算的结果,C列是计算原始日期的月份,D列是月份*10,结果转换为日期格式,E列是对D列结果求月份,如图3-12-9所示。


excel中关于日期的函数怎么用?这两个关于Excel日期函数的经典案例(5)

图3-12-9

方法七:LOOKUP函数

B1公式=MONTH(A1) C1公式=LOOKUP(B1 {1 4 7 10} {1 2 3 4})

公式解释说明:

LOOKUP函数功能是返回向量(单行区域或单列区域)或数组中的数值,有3个参数,函数语法LOOKUP(要查找的值,在哪里查找,相对位置的值)。

如果是找不到要查找的,会从后向前查找到比它小的值,如果找不到,则返回错误值。

需要提醒的是:

(1)查找的区域必须按升序排列。

…、-2、-1、0、1、2、…、A~Z、FALSE、TRUE

(2)查找的区域可以有错误值,但在查找时会被忽略。

我们再来看看公式=LOOKUP(B1 {1 4 7 10} {1 2 3 4}),如果B1等于5,在{1 4 7 10}中查找,没有这个数,就返回比5小的最大值4,而月份4对应的是第2季度。

方法八:IF函数

B1公式=IF(MONTH(A1)<4 "1" IF(MONTH(A1)<7 "2" IF(MONTH(A1)<10 "3" "4")))

公式解释说明:

如果月份小于4,就返回1,如果在4~6就返回2,7~9返回3,10~12返回4。

根据日期判断闰年还是平年

怎样自动计算某日期所在年的天数,即判断是闰年还是平年。

方法一:DATE函数

公式

=DATE(YEAR(A1) 12 31)-DATE(YEAR(A1) 1 1) 1

A1单元格为日期,YEAR函数取该日期所在的年份,用该年的12月31日减去1月1日便是从年初到年末的间隔天数,加1就是该年的总天数。

方法二:IF函数

公式

=IF(OR(AND(MOD(YEAR(A1) 4)=0 MOD(YEAR(A1) 100)<>0) MOD(YEAR(A1) 400)=0) "366" "365")

用IF函数判断该日期所在年份是闰年还是平年,闰年366天,平年365天。

闰年特点:

  • 四年一闰,百年不闰,四百年再闰。
  • 即能被4整除而不能被100整除;
  • 能被400整除。
  • MOD函数是求余数。
  • OR是逻辑函数,多个条件只要有一个成立就返回TRUE。
  • AND函数是多个条件同时成立。

猜您喜欢: