有朋友問了我一個(gè)計(jì)算工齡的公式，看到后感慨萬千：工作后要計(jì)算工齡，在網(wǎng)上搜索的就是這個(gè)公式“=YEAR(TODAY()-A2+2)-1900”（假定A2是入職時(shí)間），當(dāng)時(shí)研究了好久，還沒研究透為什么這樣設(shè)置。我選這個(gè)公式的原因是它很短，過去了好久回過頭來再研究，還是不怎么理解。后來知道了DATEDIF函數(shù)，就一直用這個(gè)函數(shù)來計(jì)算，包括有人問我工齡、年齡等如何計(jì)算，我也是告訴他用DATEDIF函數(shù)。
再次看到這個(gè)公式，發(fā)現(xiàn)是一個(gè)挺燒腦的公式，而且也是比較考驗(yàn)基礎(chǔ)的公式。
我們來分析一下這個(gè)公式：
1、這個(gè)公式用的函數(shù)挺簡(jiǎn)單，YEAR函數(shù)是用來求年份的，TODAY函數(shù)是返回當(dāng)前的日期。
2、TODAY()-A2+2，兩日期相減（大日期減小日期或兩個(gè)相同日期相減）返回值是一個(gè)大于等于0的數(shù)字。至于為什么加2，后面再解釋，暫且忽略。
3、我們平時(shí)看到的YEAR函數(shù)里面的參數(shù)通常是日期或引用的填充為日期格式的單元格，為什么在這里確是一個(gè)大于等于0的數(shù)字呢？
其實(shí)日期也可以看作為數(shù)字，我們?cè)趩卧裰腥我廨斎胍粋€(gè)大于等于“1900-1-0”的日期，將單元格格式設(shè)置為常規(guī)格式，我們會(huì)發(fā)現(xiàn)單元格中出現(xiàn)了數(shù)字，如分別輸入“1900-1-0”、“1900-1-1”、“2016-7-1”，單元格格式設(shè)置為常規(guī)后，分別返回0、1、42552。系統(tǒng)默認(rèn)的日期是從1900-1-1開始的（出現(xiàn)1900-1-0，是0設(shè)置為日期格式而顯示出來的），任意數(shù)字N可以看作是從1900-1-1開始向后推N-1天的日期，如數(shù)字15，就代表1900-1-1向后推14（15-1=14）天，結(jié)果為1900-1-15。
這樣我們就可以理解了，YEAR函數(shù)的參數(shù)既可以是日期（日期用英文引號(hào)引用），也可以是大于等于0的數(shù)字。所以“=YEAR(“2016-7-1”)”和“=YEAR(“1900-1-1”+42552-1)”得出的結(jié)果都是2016。
回過頭來再看，TODAY()-A2+2返回的數(shù)值N是相差的天數(shù)，同樣是在1900-1-1之后N-1天的某個(gè)日期，用YEAR函數(shù)來計(jì)算出具體是哪一年。
4、減去1900，這個(gè)就好理解了，相差的年數(shù)是在1900基礎(chǔ)上開始計(jì)算的，所以再減去1900，就是最終相差的年數(shù)，即工齡。
5、為什么要加2？這個(gè)還是基于對(duì)YEAR函數(shù)的理解，我們都知道兩個(gè)日期相差的天數(shù)，一般用大日期減去小日期再加上1即是結(jié)果。
那么肯定有人認(rèn)為“TODAY()-A2+1”即可，我們?cè)偻罂矗琘EAR函數(shù)的參數(shù)，如果正好是全年的天數(shù)，它不認(rèn)為是代表1年的，因?yàn)檫@一年還沒有過去。例如YEAR(366)，366轉(zhuǎn)換為日期為“1900-12-31”（1900年為閏年，天數(shù)為366天），我們將其單元格格式轉(zhuǎn)變?yōu)椤癥YYY-M-D HH:SS:MM”格式，我們會(huì)發(fā)現(xiàn)結(jié)果為“1900-12-31 00:00:00”，如果輸入366.1則顯示結(jié)果為“1900-12-31 00:14:24”。
看到這里大家能明白吧，數(shù)字366代表還在1900年內(nèi)，還沒有超過“1900-12-31 24:00:00”，同樣推理，后面每一年的這個(gè)節(jié)點(diǎn)都是這種情況，所以如果兩個(gè)日期相差天數(shù)正好為N年的全年日歷天數(shù)相加，那么返回的結(jié)果為N-1。
公式中加2，我們要分開來看，一個(gè)是日期相差的天數(shù)需要兩者相減再加1，另外一個(gè)是N年的日歷天數(shù)之和，YEAR函數(shù)返回N-1年，所以還需要再加1，1+1=2，于是就加了2。
這個(gè)公式，涉及了YEAR函數(shù)的深入理解、日期和數(shù)字的相互轉(zhuǎn)換理解，系統(tǒng)默認(rèn)的最小日期及應(yīng)用，小小的一個(gè)公式，涉及的知識(shí)卻不少，如果不是對(duì)EXCEL基礎(chǔ)知識(shí)的深入掌握，我想理解這個(gè)公式還是有一定難度的。
上面是我對(duì)這個(gè)公式的理解和解析，有其它意見的朋友也請(qǐng)發(fā)現(xiàn)意見，大家一起交流。
聯(lián)系客服
