2022-12-13 02:35来源:m.sf1369.com作者:宇宇
1、如图,左边是原始数据,我们要求的是右边的结果:日期分别是第几天,第几周,第几月。
2、先看如何求日期是一年的第几天。逻辑是:用当前日期-一年的第一天的日期,再加上1天,就是一年的第几天了。包含2个函数:days函数第一个对象是结束日期,第二个对象是起始日期(这里选择当年元旦那一天);起始日期我们使用date函数,对应的三个对象分别是年月日(我们用2016,1,1,逗号隔开);最终+1,相当于做差+1了,表示第几天了如图所示。
3、接着看如何求日期是一年的第几周。直接使用weeknum函数,这个函数就是表示日期是第几周了。第一个对象是日期,第二个对象表示用周日(选择1)还是周一(选择2)作为星期的第一天如图。
4、接着看如何求日期是一年的第几月。直接使用month函数,只需要一个对象:日期,如图
5、下拉公式,查看结果。我们核对一下,公式没有问题,满足了我们的要求。如图。
6、再加一个问题:如何求日期在当月第几天?这个很简单,可以直接用day函数,对象就是日期。如图
7、那么如何求日期是哪一年的日期?同样直接用year函数就可以了,对象还是日期。如图
根据一周7天来计算返回月份,需要len、left、month等函数综合处理。
Excel版本参考:2010
演示:20周转为月份
1、A1单元格录入20周;
2、B1录入公式:=VALUE(MONTH(DATE(YEAR(TODAY()),1,1)+LEFT(A1,LEN(A1)-1)*7))
回车
先创建数据的透视表,点击插入,数据透视表下拉,点击数据透视表。
02
进入创建数据透视表对话框,默认是标准所有数据的区域。点击确定。
03
选择添加需要汇总处理数据的字段。
04
进入透视表页面,在日期列,右键,选择组合,创建组合。
05
在分组对话框中可以看到日期的默认范围,因为我们要从星期一算起,所以要设置8月15日之前的第一个星期一为起始,即起始2013/8/12。同时将步长设置为天,选择7天。
06
点击确定,即得到日期按照周组合,数量的汇总了。
方法二:通过辅助列“周数”实现
01
在原始数据中增加“周数”列作为辅助列,周数代表在本年中这周是第几周。通过A列的日期使用公式得到周数。
公式为:=YEAR(A2)&"-"&TEXT(WEEKNUM(A2,2),"00")。
公式说明,YEAR(A2)表示获取A2单元格的年份,连接“-”字符,WEEKNUM(A2,2)表示一周从星期一开始,A2是本年的第几周,TEXT表示按照“00”格式输出。
02
将鼠标放置设置公式单元格的右下角,当鼠标变成黑十字时,按住左键向下拖动,以相同的公式填充周数列。
03
然后选择插入数据透视表,创建含有周数的透视表。
04
选择字段,获取按周数的数量汇总。
05
也可以不选择日期,则看到每周的总数
日期是以周日为一周的第一天计算的,所以后面加上参数2,如果是从周2开始的话就是3。
公式"=DATEDIF(A2-1,B2,"M")/12" 值栏要设置为数值类型
=DATEDIF(A2,TODAY(),"m") "TODAY()"代表当前日期
如果是月份用这个公式就可以了,如果是天的话就把“m”改成“d”年就是“y”。
公式意思是,A2到今天,是N个月。
DATEDIF函数 [英] DATEDIF function
Excel隐藏函数,在帮助和插入公式里面没有。
简要说明: 返回两个日期之间的年\月\日间隔数。
实例:
题目: 计算出生日期为1973-4-1人的年龄。
公式: =DATEDIF("1973-4-1",TODAY(),"Y")
结果: 33
简要说明 当单位代码为"Y"时,计算结果是两个日期间隔的年数。
day为0会取上个月最后一天 month为0会取上年最后一天 如果year为0,估计是取excel中可以存储的最早一年1900年吧。这个只是猜测。具体你可以试一下
假设范围是G:Z列,具体范围请根据实际需要调整。
A4公式下拉: =IF(COUNT(G4:Z4),LOOKUP(9E+307,G4:Z4,G$3:Z$3),""
) 如果用的是EXCEL 2007及以上版本,A4公式可精简为: =IFERROR(LOOKUP(9E+307,G4:Z4,G$3:Z$3),""
) 注:G到Z列第4行开始的单元格需为常规或数值格式。
如果为文本格式,A4公式需修改为:
=IF(COUNTA(G4:Z4),LOOKUP(1,0/(G4:Z4<>""),G$3:Z$3),""
) 估计第3行的是天数,最多应为31天,估计数所区是G:AJ列,公式改为: =IF(COUNT(G4:AJ4),LOOKUP(9E+307,G4:AJ4,G$3:AJ$3),"")
excel利用函数公式计算天数的方法步骤
1:首先,计算两个日期之间的天数。
2:在excel中,两个日期直接相减就可以得到两个日期间隔的天数,
如何用excel函数公式计算天数
3:计算两个日期之间的天数,也可以用隐藏函数DATEDIF函数来完成,公式如下:
=DATEDIF(A2,B2,"d")
如何用excel函数公式计算天数
4:这个函数语法是这样的:=DATEDIF(开始日期,结束日期,第三参数),第三参数可以有若干种情况,分别可以返回间隔的天数、月数和年数。
如何用excel函数公式计算天数
5:首先,最简单的是两个时间直接相减即可得到时间差。但是注意,存储结果的单元格要设置成时间格式。
如何用excel函数公式计算天数
6:上面的例子是未超过1天的时间,如果超过1天的时间利用上述的方法直接相减会得到什么结果呢?
如何用excel函数公式计算天数
7:上面的例子更常出现于考勤表的时间计算,由于两个日期可能跨越2天或者更长时间,默认显示结果不正确,需要通过设定单元格格式的方法来显示正常的时间差。如下图设置单元格格式为“[h]:mm:ss”,其中h加上方括号可以将时间的差小时数完全显示,如果不加方括号只能显示扣除天以后的小时之差。
如何用excel函数公式计算天数
8:如果想将时间差的天数和时间都显示出来,可以设置单元格格式为:
d天 h小时mm分ss秒
1,单个月的工作日天数计算。我们这里以2018年5月为例计算工作日天数(下图1)。首先,我们需要新建一个辅助列,内容为要去除的日期(下图2)。
2,我们使用NETWORKDAYS函数来计算工作日天数。第1参数为开始日期,即:每月第1天。第2参数为结束日期,即:每月最后1天。第3参数为要去除的日期,即:每月的国假日。
3,函数的计算结果可能会显示为日期格式,我们可以使用"Ctrl+1"组合快捷键设置单元格格式为"常规"。
4,"常规"的单元格格式是有小数就显示小数(一般显示7位小数),无小数就不显示小数。也可以设置单元格格式为"数值",默认的是两位小数,我们可以设置小数位数。最终的计算结果见下图。
WEEKNUM 返回一个数字,该数字代表一年中的第几周。
如果该函数不可用,并返回错误值 #NAME?,请安装并加载“分析工具库”加载宏。操作方法 在“工具”菜单上,单击“加载宏”。在“可用加载宏”列表中,选中“分析工具库”框,再单击“确定”。如果必要,请遵循安装程序中的指示。tools-macro, 里面就以下几个选项: Macro record new macro security visial basice editor macrosoft script editor “工具”菜单,就是“data/table”左手边那个,下拉,然后找到“加载宏”(add-in),点击进去,在提示要你加载的程序前打个勾,你你如果不知道是哪个的话,就全部都勾上就没错了。
1.选中日期所在区域,在【开始选项卡】,选择【数值类型】下拉选项;
2.在【其他数据格式】进行数据【自定义】设置为aaaa;
3.点击【确定】后,即可看到自定义数值类型后的星期显示状态;
4.在单元格使用text函数改变表现形式“=TEXT(A2,"aaaa")”;
5.回车后,使用向下拖拽的方式即可得到相关星期的结果。