excel函數日期與時間相關計算匯總

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 skq4、確定一個日期的季度：=ROUNDUP(MONTH(A1)/3,0)5、本年:=year(now());本月:=month(now());本日:=day(now());今天星期幾:=weekday(now(),2) BYLWQHLWQH6、身份證年齡計算：=DATEDIF(TEXT(MID(B14,7,6+(LEN(B14)=18)*2),"#-00-00"),TODAY(),"y") BYgvntw7、準確計算一個人的年齡：=DATEDIF（A1，TODAY（），「Y」）8、A1=20040101格式轉為日期格式數值： =text(A1."0000-00-00")*1 BY fieldsun9、返回上月月末日期：=Today()-Day(Today()) 返回上月天數：=Day(Today()-Day(Today()))需要載入「工具－選項－載入宏工具分析庫」： 返回本月月末日期：=Eomonth(today(),0) 返回當前日期10天後的工作日：=Workday(today(),10) 返回當前日距月末還有多少個工作日：=Networkdays(Today(),Eomonth(today(),0)) BY gdliyy10、將日期轉為序數（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) BYslhenry12、從身份證號碼中取出出生年月日：=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 BYgvntw13、根據出生年份，推出所屬生肖=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,"鼠","牛","虎","兔","龍","蛇","馬","羊","猴","雞","狗","豬") BY9101914、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 fieldsun3、時間向上進位到整時=ceiling(a1,1/24) BY fieldsun 時間15分鐘以下舍動.15分鐘進為半小時,45分鐘進為一小時=floor(a1+1/96,1/48)