男科疾病急性前列腺炎:excel函数日期与时间相关计算汇总

来源:百度文库 编辑:欧普网 时间:2019/09/21 13:43:10

excel函数日期与时间相关计算汇总2010-06-19 21:33

转载: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)