转载:excel函数日期与时间相关计算汇总
一、日期：
1、确定一个月的最后一天：=DATE（YEAR(A1),MONTH(A1)+1,0）
2、确定最近的星期日日期：=TODAY()-MOD(TODAY()-1,7)
3、两个日期间的间隔天数计算：=datedif("开始日期","终止日期","d") BY skq
4、确定一个日期的季度：=ROUNDUP(MONTH(A1)/3,0)
5、本年:=year(now());本月:=month(now());本日:=day(now());今天星期几:=weekday(now(),2) BYLWQHLWQH
6、身份证年龄计算：=DATEDIF(TEXT(MID(B14,7,6+(LEN(B14)=18)*2),"#-00-00"),TODAY(),"y") BYgvntw
7、准确计算一个人的年龄：=DATEDIF（A1，TODAY（），“Y”）
8、A1=20040101格式转为日期格式数值： =text(A1."0000-00-00")*1 BY fieldsun
9、返回上月月末日期：=Today()-Day(Today()) 返回上月天数：=Day(Today()-Day(Today()))
需要加载“工具－选项－加载宏工具分析库”： 返回本月月末日期：=Eomonth(today(),0) 返回当前日期10天后的工作日：=Workday(today(),10) 返回当前日距月末还有多少个工作日：=Networkdays(Today(),Eomonth(today(),0)) BY gdliyy
10、将日期转为序数（A26是日期）BYslhenry
=TEXT(A26,"mmmm ")&DAY(A26)&IF(INT(MOD(DAY(A26),100)/10)=1,"th ",IF(MOD(DAY(A26),10)=1,"st ",IF(MOD(DAY(A26),10)=2,"nd ",IF(MOD(DAY(A26),10)=3,"rd ","th "))))&TEXT(A26,",yyyy ")
11、计算某月里第几周的天数（如B31是2004-8-1，B32是5）用以下公式得到结果3
=SUM((MONTH(B31-WEEKDAY(B31)+{1,2,3,4,5,6,7}+7*(B32-1))=MONTH(B31))*1) BYslhenry
12、从身份证号码中取出出生年月日：=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 BYgvntw
13、根据出生年份，推出所属生肖
=VLOOKUP(MOD(A1-1971,12),{1,"鼠";2,"牛";3,"虎";4,"兔";5,"龙";6,"蛇";7,"马";8,"羊";9,"猴";10,"鸡";11,"狗";0,"猪"},2,0) BYttt
=Choose(Mod(Year(A1)-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪") BY91019
14、BY91019
求两日期间有几个星期
A1为 开始日期
A2为 结束日期
星期日=int((Weekday(A1-0,2)+A2-A1)/7)
星期一=int((Weekday(A1-1,2)+A2-A1)/7)
星期二=int((Weekday(A1-2,2)+A2-A1)/7)
星期三=int((Weekday(A1-3,2)+A2-A1)/7)
星期四=int((Weekday(A1-4,2)+A2-A1)/7)
星期五=int((Weekday(A1-5,2)+A2-A1)/7)
星期六=int((Weekday(A1-6,2)+A2-A1)/7)
求两日期间有几个星期
A1为 开始日期
A2为 结束日期
星期日=int((Weekday(A1-0,2)+A2-A1)/7)
星期一=int((Weekday(A1-1,2)+A2-A1)/7)
星期二=int((Weekday(A1-2,2)+A2-A1)/7)
星期三=int((Weekday(A1-3,2)+A2-A1)/7)
星期四=int((Weekday(A1-4,2)+A2-A1)/7)
星期五=int((Weekday(A1-5,2)+A2-A1)/7)
星期六=int((Weekday(A1-6,2)+A2-A1)/7)
求两日期间有几个星期
A1为 开始日期
A2为 结束日期
星期日=int((Weekday(A1-0,2)+A2-A1)/7)
星期一=int((Weekday(A1-1,2)+A2-A1)/7)
星期二=int((Weekday(A1-2,2)+A2-A1)/7)
星期三=int((Weekday(A1-3,2)+A2-A1)/7)
星期四=int((Weekday(A1-4,2)+A2-A1)/7)
星期五=int((Weekday(A1-5,2)+A2-A1)/7)
星期六=int((Weekday(A1-6,2)+A2-A1)/7)
15、BY91019
已过的最近的星期天=today()-Weekday(today(),2)
未过的最近的星期天=today()+7-Weekday(today(),2)
已过的最近的星期天=today()-Weekday(today(),2)
未过的最近的星期天=today()+7-Weekday(today(),2)
已过的最近的星期天=today()-Weekday(today(),2)
未过的最近的星期天=today()+7-Weekday(today(),2)
--------------------------------------------------------------------------------
二、时间：
1、在原来的时间上加两个半小时 =A1+time（2，30，0）
2、向下舍
小时数
=TEXT($A$1,"")*1或 =INT(A1*24) 分种数 =TEXT($A$1,"
")*1 秒数 =TEXT($A$1,"")*1 向上进
小时数 =CEILING(TEXT($A$1,".mmss"),1) 分钟数 =CEILING(TEXT($A$1,"
.ss"),1) 四舍五入(30以上进位)
小时数
=ROUND(TEXT($A$1,".mmss")+0.2,0) 分种数 =ROUND(TEXT($A$1,"
.ss")+0.2,0) BY fieldsun
3、时间向上进位到整时=ceiling(a1,1/24) BY fieldsun 时间15分钟以下舍动.15分钟进为半小时,45分钟进为一小时=floor(a1+1/96,1/48)
