日期函数.xls

应用例子1

日期 公式 公式
▲ 2005/2/23三 2005/2/20日 =$B4-MOD($B4-1,7)
2005/2/27日 2005/2/20日 =$B5-(WEEKDAY($B5,2)=7)*WEEKDAY($B5,2)

▲ 2005/1/2日 2005/1/2日 =$B7+(7-WEEKDAY($B7,2))
2005/1/3一 2005/1/9日 =$B8+(7-WEEKDAY($B8,2))

▲ 2003 不是闰年 =IF(MONTH(DATE($B32,2,29))=2,"闰年","不是闰年")

▲ 2005/2/28 =IF(TODAY()-$B43=0,"今天到期",IF(TODAY()-$B43<0,"还有"&$B43-TODAY()&"天到期",""))

▲ 2005/4/24 2 =ROUNDUP(MONTH($B34)/3,0)
2005/2/24 1 =ROUNDUP(MONTH($B35)/3,0)
2004/11/3 4 =CEILING(MONTH(C16)/3,1)

▲ 2005/6/2四 2005/6/20一 =DATE(YEAR($B10),MONTH($B10),1)+2-WEEKDAY(DATE(YEAR($B10),MONTH($B10),1))+(3-(2>=WEEKDAY(DATE(YEAR($B10),MONTH($B10),1))))*7
2006 2006/5/14日 =DATE(2005,3,1)+IF(6<WEEKDAY(DATE(2005,3,1)),7-WEEKDAY(DATE(2005,3,1))+6,6-WEEKDAY(DATE(2005,3,1)))+(3-1)*7


▲ 2005/1/26三 5 =INT((WEEKDAY((EOMONTH($B21,-1)+1)-6,2)+EOMONTH($B21,0)-(EOMONTH($B21,-1)+1))/7)
2005/8/3三 5 {=SUM((WEEKDAY(DATE(YEAR($B12),MONTH($B12),1)+ROW(INDIRECT("1:"&DAY(DATE(YEAR($B12),MONTH($B12)+1,0))))-1)=2)*1)}
2005/8/4四 4 {=SUM((WEEKDAY(DATE(YEAR($B13),MONTH($B13),ROW(INDIRECT("1:"&DAY(EOMONTH($B13,0))))))=1)*1)}

▲ 2005/1/1 2 =SUM((MONTH($B16-WEEKDAY($B16)+($B17-1)*7+{1,2,3,4,5,6,7})=MONTH($B16))*1)
第6周

▲ 2005/2/23 February 23rd ,2005 =TEXT($B19,"mmmm ")&DAY($B19)&IF(INT(MOD(DAY($B19),100)/10)=1,"th ",IF(MOD(DAY($B19),10)=1,"st ",IF(MOD(DAY($B19),10)=2,"nd ",IF(MOD(DAY($B19),10)=3,"rd ","th "))))&TEXT($B19,",yyyy ")
2005/2/1 February 1st ,2005
2005/2/2 February 2nd ,2005
2005/2/11 February 11th ,2005
2005/2/21 February 21st ,2005
2005/2/21 Feb 21st ,2005 =TEXT($B24,"mmm ")&DAY($B24)&IF(OR(VALUE(RIGHT(DAY($B24),2))={11,12,13}),"th ",IF(OR(VALUE(RIGHT(DAY($B24)))={1,2,3}),CHOOSE(RIGHT(DAY($B24)),"st ","nd ","rd "),"th "))&TEXT($B24,",yyyy ")
2005/2/2 Feb 2nd ,2005
2005/2/13 Feb 13th ,2005

▲ 2005/1/1 2005/4/6三 =$B28-WEEKDAY($B28)+(15-1)*7+4
2005/1/1 2005/4/6三 =$B29-WEEKDAY($B29,2)+(15-1)*7+3