エクセルによる勤続年月の計算

経理課の課長に頼まれた、エクセルによる勤続年月の計算。
入社年月日と退社年月日を入力すると、勤続が表示される、というもの。

条件1：

入社の日付から次の月の同じ日付の前日まで、が1ヶ月とする。
例えば、入社が2000年4月5日とすると、2000年5月4日までが勤続1ヶ月目とする。
つまり、2000年5月5日になると勤続2ヶ月目になる。

条件2：

入社日が月末の場合、翌月の月末の前日までが1ヶ月。
例えば、入社が2000年5月31日とすると、2000年6月29日までが勤続1ヶ月目。
2000年6月30日になると勤続2ヶ月目になる。

条件3：

入社日が3月29～30日の場合、2月末日の前日までが1ヶ月。
例えば、入社が2000年3月29日とすると、2001年2月27日までで、勤続11ヶ月目となる。
2001年2月28日で、勤続1年になる。

計算式

これをエクセルでVBAを使わずに計算させるには、計算式が長すぎて、1回の計算式では実現できなかった。

(1)勤続年を計算
セルに以下の式を入力し、セルの名前を「仮勤続年」と定義する。
=IF(OR(入社="",退社="",退社<入社),"",IF(MONTH(退社)<MONTH(入社),YEAR(退社)-YEAR(入社)-1,YEAR(退社)-YEAR(入社)))

(2)勤続月を計算
セルに以下の式を入力し、セルの名前を「仮勤続月」と定義する。
=IF(OR(入社="",退社="",退社<入社),"",IF(MONTH(退社)<MONTH(入社),12+MONTH(退社)-MONTH(入社),MONTH(退社)-MONTH(入社)))

(3)調整（月）を計算
セルに以下の式を入力し、セルの名前を「調整」と定義する。
=IF(OR(入社="",退社="",退社<入社),"",IF(AND(OR(DAY(入社)=DAY(DATE(YEAR(入社),MONTH(入社)+1,1)-1),AND(MONTH(入社)=3,OR(DAY(入社)=29,DAY(入社)=30))),DAY(退社)=DAY(DATE(YEAR(退社),MONTH(退社)+1,1)-1)),1,IF(DAY(入社)<=DAY(退社),1,0)))

(4)(1)～(3)から勤続年月を表示
セルに以下の式を入力する。
=IF(OR(入社="",退社="",退社<入社),"",IF(仮勤続月+調整>=12,仮勤続年+(調整+仮勤続月-12+1)&"年"&(仮勤続月+調整-12)&"ヶ月",仮勤続年&"年"&仮勤続月+調整&"ヶ月"))

セルの名前の定義

セルに名前を定義する場合、1行に1名の社員を入力して、勤続年月を社員分表示させたい場合には、列全体を選択して「挿入」→「名前」→「定義」で、列全体に名前を設定する。
その名前を指定すると、同じ行のその名前を定義してある列が参照される（別の行は参照できないので注意）。