2011年5月9日 星期一

Excel-日期計算的練習

根據一個日期,試著來練習幾個日期的運算(參考下圖)。

(1) 計算月份

儲存格B2:=MONTH(B1)

(2) 本月天數

儲存格B3:=DAY(DATE(YEAR(B1),MONTH(B1)+1,0))

(3) 前1個月最後1天和本月最後1天

儲存格B4:=DATE(YEAR(B1),MONTH(B1),0)

儲存格B5:=DATE(YEAR(B1),MONTH(B1)+1,0)

(4) 本月第1個工作天和本月最後1個工作天

儲存格B6:=DATE(YEAR(B1),MONTH(B1),1)+CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1),1),2),0,0,0,0,0,2,1)

WEEKDAY函數中的參數2,是指1~7表示為星期一~星期日。利用CHOOSE函數,根據WEEKDAY函數如果傳回值是6,7(假日),則以該月的第一天,分別加上2,1,才是真正的工作天。

儲存格B7:=DATE(YEAR(B1),MONTH(B1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5))

WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5)是將1~7來減掉5,再和0取最大值。也就是如果WEEKDAY傳回6,7(假日),則會結果為2,1。取下個月的第一天,來減掉2或1個工作天。 

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六)之間的整數。

語法:WEEKDAY(serial_number,[return_type])

Serial_number:要找的日期的代表序列值。

Return_type:決定傳回值類型的數字。

 

RETURN_TYPE

傳回的數字

1或省略

數字1(星期日)7(星期六)

2

數字1(星期一)7(星期日)

3

數字0(星期一)6(星期六)

11

數字1(星期一)7(星期日)

12

數字1(星期二)7(星期一)

13

數字1(星期三)7(星期二)

14

數字1(星期四)7(星期三)

15

數字1(星期五)7(星期四)

16

數字1(星期六)7(星期五)

17

數字1(星期日)7(星期六)

 

(5) 距年初天數和距年底天數

儲存格B8:=B1-DATE(YEAR(B1)-1,12,31)

儲存格B9:=DATE(YEAR(B1),12,31)-B1

(6) 距年初月數和距年底月數

儲存格B10:=DATEDIF(DATE(YEAR(B2),1,1),B1,"YM")

儲存格B11:=DATEDIF(B1,DATE(YEAR(B1),12,31),"YM")

Datedif

DATEDIF(start_date,end_date,unit)

Unit  說明

"Y" 週期中的整年數

"M" 週期中的整月數

"D" 週期中的天數

"MD"start_dateend_date間的天數差。(忽略日期中的月和年)

"YM"start_dateend_date間的月數差。(忽略日期中的日和年)

"YD"start_dateend_date間的天數差。(忽略日期中的年)

沒有留言:

張貼留言

檢視其他文章

好康東東