錦子老師

在補習班一位學生在月中提出了一個蠻不錯的問題！她說：錦子老師，如果我們公司計算的基準是以每年的1月31日來計算其可休假天數，則公式要如何寫呢？

其實只要運用DATEDIF、DATE、YEAR、IF、IFERROR、VLOOKUP函數即可完成這個公式的計算。

1、新增一張工作表，輸入各個年資對應的可休假天數表格(目前放在SHEET2)。

2、點取B3儲存格輸入公式：

=IFERROR(IF(TODAY()<DATE(YEAR(TODAY()),1,31),DATEDIF(A3,DATE(YEAR(TODAY()-1),1,31),"M")/12,DATEDIF(A3,DATE(YEAR(TODAY()),1,31),"M")/12),0)

再將公式複製到B4:B12儲存格。

【公式解說】

DATEDIF(A3,DATE(YEAR(TODAY()),1,31),"M")/12 傳回系統日期當年1月31日減去A3儲存格的日期之間差距月份除以12的值。

IF(TODAY()<DATE(YEAR(TODAY()),1,31),DATEDIF(A3,DATE(YEAR(TODAY()-1),1,31),"M") /12,DATEDIF(A3,DATE(YEAR(TODAY()),1,31),"M")/12) 如果系統日期小於當年度1月31日，則傳回系統日期前一年1月31日減去A3儲存格的日期之間差距月份除以12的值，否則傳回系統日期當年1月31日減去A3儲存格的日期之間差距月份除以12的值。

IFERROR(IF(TODAY()<DATE(YEAR(TODAY()),1,31),DATEDIF(A3,DATE(YEAR(TODAY()-1),1,31),"M")/12,DATEDIF(A3,DATE(YEAR(TODAY()),1,31),"M")/12),0) 如果A3儲存格日期大於系統日期當年1月31日則傳回0。

3、點取C3儲存格輸入公式：

=VLOOKUP(B3, Sheet2!$A$2:$B$28, 2, TRUE)

再將公式複製到C4:C12儲存格。

部落格相關範例