「乾貨技巧」Excel中的日期運算與提醒

在製作Excel表格時，日期是辦公使用者經常遇到的要素，同時日期還經常要參與運算，如簽訂合同、計算合同到期日、領取證書、要注意換證日等。同時，即便設定了到期日期，工作一忙起來，也容易因檢視不仔細，而導致合同、證書過期，那問題可就大了，日期運算、到期提示等，那可是相當重要啊。下面就以例項來看看日期的運算與提醒方法吧。

到期日期計算

首先，自然是建立Excel表格，在輸入簽約日期和合同期後，使用者就不需要輸入到期日期，直接計算出到期日期就可以了。可在到期日期中輸入“=DATE（YEAR（B2）+C2，MONTH（B2），DAY（B2）-1）”這一公式（圖1），這個公式很簡單，即提取B2單元格中的年份、月份和日期，並直接與合同期累加就可以了。

小提示：如合同期是以年計算，那麼，直接輸入YEAR（B2）+C2就可以得到到期年份，相應地，如果是以月計算，那輸入MONTH（B2）+C2就可以了，不用考慮進位問題，這一函式會自動進位。另外在日期計算中，一定要使用DAY（B2）-1，畢竟到期日是要提前一天的。

事情結束了嗎？沒有，因為合同的有效期並不都以年來計數，還經常用到月、天這樣的日期單位，面對這種情況該怎麼辦？首先介紹一下字元段擷取語句，就是LEFT或RIGHT，其基本格式為LEFT/RIGHT （引用單元格，擷取位數），其中LEFT為從左端擷取，RIGHT為從右端擷取。而LEN函式則是獲得字串中長度，使用這兩個函式，可以用RIGHT（C2，1）來獲得合同期的單位，看是年還是日、月，用LEFT（C2，LEN（C2）-1）函式來獲得日期長度，以便於計算。

剩下的，就要要用IF函式來進行單位判定，這條函式有點長，為：=IF（RIGHT（C4，1）=“年”，DATE（YEAR（B4）+LEFT（C4，LEN（C4）-1），MONTH（B4），DAY（B4）-1），IF（RIGHT（C4，1）=“月”，DATE（YEAR（B4），MONTH（B4）+LEFT（C4，LEN（C4）-1），DAY（B4）-1），IF（RIGHT（C4，1）=“日”，DATE（YEAR（B4），MONTH（B4），DAY（B4）+LEFT（C4，LEN（C4）-1）-1））））。

其實這條函式很簡單，就是說如果合同期單位是年，那麼將截獲的數字加在年上，如果是月，加在月上，如果是日則加在日上，這樣在合同日期中不管輸入的是年、月、日等任何單位，都可以獲得正確的到期日期。

到期提醒設定

有了到期日期，最好有個到期提醒，即臨近到期時，Excel能夠自動進行提醒，以免在眾多日期中不小心看漏了，從而造成損失。這裡介紹變色法，即臨近到期日時，到期日期會自動變色。

要實現這一目的，可以在開始選單中找到並下拉“條件格式”選單，選擇“管理規則”後，再選擇“新建規則”中的“使用公式確定要設定格式的單元格”（圖2），隨後在“為符合此公式的值設定格式中”輸入公式”=AND（$D2>TODAY（），$D2-TODAY（）<10）”，這一公式的作用就是對比當前日期和D2單元格日期，當發現D2日期在當前日期之後，且距離在10天內輸出訊號，此時使用者可點選下方的“格式選項”，在“填充”中選擇適當的顏色（一般為紅色），這樣一旦到期日期在10天之內，單元格就是變為紅色。

當然，由於合同的規模不同，一些大規模合同要提前安排才能生產，這樣就要求有一個到期日期的檢視才更人性化。而這一倒計時的公式很簡單，為=D2-TODAY（）&“天到期”，即將合同到期日期減去當前日期，並加上“天到期”字樣，就可以了（圖3）。