如下圖所示的自動更新日期和星期的考勤表。

當O1單元格的月份更新時，AD1單元格的當月天數自動更新，B2:AF3的日期和星期也自動更新。當月份為2月時，B3:AF3隻顯示到第28天，當月份為4月時，B2:AF3顯示到第30天，當月份為5時，B2:AF3顯示到第31天。

要制作這種動态更新日期和星期的考勤表，需要用的函數有Date函數、Weekday函數、Day函數、IF函數。還需要用到條件格式。

第一步：制作如下圖所示的表格

在J1單元格輸入2021，選中J1:M1設置單元格對齊方式為跨列居中；在AA1單元格輸入文本“當月天數”，選中AA1:AC1設置單元格對齊方式為跨列居中。在B3:AF3輸入數字1-31代表5月份的1号至31号。

第二步：設置自動更新的當月天數

在單元格AD1輸入公式：=day(date(J1,O1 1,1)-1)

公式解析：1.date函數返回一個日期，語法為date(year,month,day)。本例中J1=2021，O1=5，date(J1,O1 1,1)返回2021/6/1。日期本質是一個連續的數值，date(J1,O1 1,1)-1指的是返回2020/6/1前一天的日期，即2021/5/31。

2.Day函數返回一個月中的第幾天的數值，Day(2021/5/31)返31，代表的是5月的第31天。

一個月份可能有31天、30天、29天、28天，那麼如何确定某月的最後一天究竟是多少号呢？可行的方法是用下個月1号減1就是該月最後一天的日期，再用Day函數即可确定該月的的天數。

第三步：設置自動更新的星期

在B2單元格輸入公式：=IF(WEEKDAY(DATE($J$1,$O$1,B$3),2)=7,"日",WEEKDAY(DATE($J$1,$O$1,B$3),2))

函數解析：1.DATE($J$1,$O$1,B$3)返回日期2021/5/1；

2.weekday返回代表一周中的第幾天的數值

WEEKDAY(DATE($J$1,$O$1,B$3),2)，此處weekday("2021/5/1",2)返回6，weekday函數的參數2代表以星期一為第一天，星期日為第7天。

3.當某一日期為周日時，weekday函數返回7，不符合我們的使用習慣，因此使用if函數。IF函數代表，當weekday函數返回值為7時，返回“日”，否則，返回weekday函數的值。

注意該公式中絕對引用和相對引用的使用。拖動B2單元格填充柄，将B2單元格公式複制到其他C2:AF2單元格。

第四步：設置中文小寫

第三步中在B2:AF2設置公式返回的值為1、2、3、4、5、6、日。如何将其設置為一、二、三、四、五、六、日呢？

選中B2:AF2單元格區域，按Ctrl 1打開【設置單元格格式】對話框，設置為【中文小寫數字】

第五步：設置條件格式

第一步中在B3:AF3輸入1-31，但是有的月份并沒有31天，可能為28天、29天、30天。也就是說，AD2:AF3并不需要每個月都顯示，例如當月份為2月時，隻需要顯示B2:AC3單元格區域的星期和日期。

要想達到上述目的，可以在AD2:AF3設置條件格式。

選中AD2:AF3單元格區域，打開設置單元格格式對話框，選擇【使用公式确定要設置格式的單元格】，輸入公式 =AD$3>$AD$1，注意公式的相對引用。

點擊【格式】，設置字體為白色。

當AD3:AF3單元格的值大于當月天數時,AD2:AF3單元格的字體設置為白色，這樣就與表格背景色一緻。

以上就是本教程的全部内容。你學會了嗎？