226 使用Excel函數計算年齡(年資)的三種方法 @ 錦子老師

226	使用Excel函數計算年齡(年資)的三種方法

在Excel中利用系統時間和出生年月來計算年齡是人事管理單位、薪資統計中經常遇到的工作，本人由於工作關係對此有些研究，現將有關計算方法介紹如下，供讀者朋友們大家共同參考討論，在前面的日子中介紹過DATEDIF函數如何計算年資，也可以計算年齡，現在再介紹三種方式計算年齡。

n 利用DAYS360、CEILING和TRUNC函數

DAYS360函數

函數類型：日期及時間

說明： 本函數依照每年360天(每月30天)計算出兩個日期間的天數，作為計算年齡(年資)的函數非常方便。

語法： DAYS360(Start_date，End_date，Method)

參數： Start_date是計算的起始日期。
End_date是計算的結束日期。
Method用來指定計算方法的邏輯值(取FALSE或忽略使用美國方法，取TRUE則使用歐洲方法)。

CEILING函數

函數類型：數學與三角

說明： 將指定數值依指定乘算基數無條件捨去。

語法： CEILING(Number，Significance)

參數： Number為待計算的數值。
Significance確定取整計算的倍數。

特別提醒： 本函數可將Number沿著絕對值增大的方向，計算出一個最接近(或最小倍數Significance)的整數。

TRUNC函數

函數類型：數學與三角

說明： 將小數位數的指定部分截去，計算出一個最接近的整數或小數。

語法： TRUNC(Number，Num_digits)

參數： Number為待計算的數值。
num_digits用於指定小數部分的截取精度，為0時不保留小數、為1時保留一位小數(依次類推)。

特別提醒： 本函數可將Number沿著絕對值增大的方向，計算出一個最接近(或最小倍數Significance)的整數。

1. 虛年資

在下圖E欄中，我們要計算「虛年資」，則E2儲存格的公式為：“=CEILING((DAYS360(C2，D2))/360，1)”。

公式中的C2和D2分別存放在公司(工廠)的到職日與離職日，「DAYS360(A1，B1)」計算兩個日期間的天數，「(DAYS360(A1，B1))/360」則按一年360天計算出年資。由於工資一般以年為單位，故用CEILING函數將上面的計算結果(沿絕對值增大的方向)取整，從而得出「虛年資」。

2. 實年資

在下圖E欄中，我們要計算「實年資」，則E2儲存格的公式為：「=TRUNC((DAYS360(C2，D2))/360，0)」，公式中計算年資天數的方法與虛年資的是相同。

TRUNC函數將「(DAYS360(C2，D2))/360」的計算結果截去小數部分，從而得出「實年資」。如果計算結果需要保留一位元小數，只須將公式修改為「=TRUNC((DAYS360(A1，B1))/360，1)」即可。

n YEAR和RIGHT函數

YEAR函數

函數類型：日期及時間

說明： 計算出日期序列數(如兩個日期相減的結果)所對應的年數。

語法： YEAR(Serial_ number)

參數： Serial_number為待計算的日期序列數，既可以是一個具體的數值，也可以是一個運算式。

RIGHT函數

函數類型：文字

說明： 該函數用來提取字串最右邊的若干個字元。

語法： RIGHT(Text，Num_chars)

參數： Text是待計算的字串。
Num_chars用來指定從右向左提取的字串長度(忽略時取1)，例如「=RIGHT(“光華商職”，3)」，完成輸入則傳回「光華商」字串。

1. 實年資

由於YEAR和RIGHT二個函數的特點，它們組成的公式只能計算「實年資」而無法計算「虛年資」，具體形式為「=RIGHT(YEAR(D2-C2)，2)」，如下圖所示。

公式中的D2和C2分別存放年資的離職日和到職日(正好與DAYS360函數相反)，「YEAR(A1-B1)」計算出兩個日期間的年份數，「RIGHT(YEAR(A1-B1)，2)」則通過自右向左提取年份的後兩位(年資一般都是一位或兩位數)。

因為YEAR函數的計算結果帶有19等字樣，必須利用RIGHT函數將它過去除。

計算出來的結果有的記錄為一位數，則年資前面會補個「0」，與一般習慣不太相符，這是上述公式的缺點。

n N和INT函數

N函數

函數類型：資訊

說明： 本函數屬於資訊函數之列，它可以完成儲存格物件的轉換，就是將數值轉換成數位，日期轉換成序列值，TRUE轉換成1，其它物件轉換成0。

語法： N(Value)

參數： Value是待轉換的儲存格物件，它可以是數值、日期等資料，也可以是一個運算式。

INT函數

函數類型：數學及三角

說明： 該函數可以將一個數值向下取整為最接近的整數。

語法： INT(Number)

參數： Number是傳回整數的一個實數或運算式，用它將計算結果擷取為整數非常方便。

由於INT函數的特點，用N和INT二個函數構成的公式只能計算「實年資」，具體形式為「=INT((N(D2-C2))/365)」。公式中的D2和 C2分別存放年資的離職日及到職日，「N(D2-C2)」計算年資的序列數(其實就是年資的天數)，「(N(A1-B1))/365」將「N(A1-B1)」的計算結果轉換為年資，由於年資數一般是小數，於是通過INT函數將它小數向下捨去取最接近的整數。