EXCEL公式運算

設定公式日子後有括號星期如(三)的公式有誤，WEEKDAY函數的輸入日期須為Sheet2的C1，令同一公式中TEXT和WEEKDAY函數都是參照同一日期，所以應在Sheet1的C1輸入：
=TEXT(Sheet2! C1,"d/m/yyyy")&" ("&CHOOSE(WEEKDAY(Sheet2!C1,1),"日","一","二","三","四","五","六")&")"
WEEKDAY函數是計算出星期幾，星期日是1、星期一是2、…、星期六是7，CHOOSE函數可按WEEKDAY函數傳回值順序配給星期幾。

至於因日期加了(三) 而出#REF！未有結果，可將公式修改如下(把日期轉成文字串比較)：
=IF(TEXT(Sheet2!C1,"d/m/yyyy")=LEFT(C$1,LEN(TEXT(工作表2!C1,"d/m/yyyy"))),"√","")

至於星期六、日及公眾假期的日子在儲存格會出現不同的顏色顯示，可用條件格式(conditional formatting)來設定：
1. Select the cells you want to format, in this case cells C1:P1 having dates.
2. Click Conditional formatting > New Rule...
3. In the New Formatting Rule window, select “Use a formula to determine which cells to format”.
4. Enter the formula in the corresponding box.
=IF(ISNUMBER(FIND("六",C1)),1,0)+IF(ISNUMBER(FIND("日",C1)),1,0)+IF(ISNUMBER(FIND("公眾假期",C1)),1,0)
5. Click the Format... button to choose your custom format.
6. Switch between the Font, Border and Fill tabs and play with different options such as font style, pattern color and fill effects to set up the format that works best for you. In this case go to the Fill tab and choose red colour.
7. Make sure the Preview section displays the format you want and if it does, click the OK button to save the rule. If you are not quite happy with the format preview, click the Format... button again and make the edits.
8. Check if the conditional format is effected (星期六、日及公眾假期日子的儲存格出現紅色)，if only cell C1 is effected, check the conditional formatting > Manage rules to see if the “applies to” cell range is $C$1$P$1.