勤続年数を計算する方法
Excelで２つの日付の差を求める場合に、勤続年数のように年月数で求めたい場合、結構難儀します。
方法としてDATEDIF関数で引数”YM”を指定する方法と、EXCEL関数を複数組み合わせる方法の、２通りがあります。しかしいずれの方法も問題があります。それは、DATEDIF関数は正しく計算できない場合があり、EXCEL関数を組み合わせる方法はメンテナンスが大変です。
以下ではこのような問題を解消したVBAで作成した関数を紹介します。
DATEDIF関数のようにセルに入力して勤続年数を求めることも可能ですし、VBA内の処理で使うことももちろん可能です。
なお、DATEDIF関数の何が問題なのかも簡単にですが最後に説明します。
Excelの日付計算は人間の感覚と違う場合がある
先に、Excelは日付の計算と人間の感覚は違う場合がある、ということを書いておきます。
２つの日付の差を求める場合、一般的な感覚では「その間の日数」を求めます。例えば、1月1日と1月2日の日数差は1日です。これはExcelの仕様と同じです。
ところが、「1月1日から1月2日までは何日ありますか？」と聞かれたら、2日、という答えがほしいです。勤続年数の考え方はこれです。ここがExcelの仕様とは異なります。
日数差の場合は２つの日付自体を含めませんが、何日間かを求める場合（勤続年数の考え方）は、指定した日付自体を含めます。
このように人間は感覚的に日数差なのか何日間なのかのどちらを要求しているかを理解できますが、Excelの日付の処理は「日数差」を求めます。
そのため、DATEDIF関数を使う場合「DATEDIF(開始日付, 終了日付 + 1, “YM”)」なんてことをして＋１の調整を入れる妙なテクニックが必要になります。
以下のVBAの関数は勤務年数の考え方で作成しています。「終了日付＋１」のような記述は不要になります。
VBAでの勤続年数の算出方法
以下は２つの日付の差を求める関数で、引数に開始日付と終了日付を渡すと、ExcelのDATEDIF関数で単位を”YM”で指定した場合のように「〇年〇か月」という結果を返します。
DATEDIF関数にはいくつかの問題や使い勝手の悪さがありますが、以下のようにそれらを解消しています。
- うるう年に対応している。
- 日数差の１か月を正しく計算する。（1/28～2/27は1か月と判定するようにしている。Excelは2/28にならないと1か月とみなさない。）
- 終了日付＋１、のような日付調整をしなくてよい。
- 開始日付と終了日付が逆転している場合はエラーにならずマイナス差を返すようにしている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
Function DateDifYM(ByVal a_sStartDate As String, ByVal a_sEndDate As String) As Variant Dim sStartDate As String '// 開始日付 Dim sEndDate As String '// 終了日付 Dim sEndNextDate As String '// 終了日付の翌日 Dim iDiffYear As Integer '// 年数差 Dim iDiffMonth As Integer '// 月数差 Dim iStartYear As Integer '// 開始年 Dim iStartMonth As Integer '// 開始月 Dim iStartDay As Integer '// 開始日 Dim iEndYear As Integer '// 終了年 Dim iEndMonth As Integer '// 終了月 Dim iEndDay As Integer '// 終了日 Dim iReverseFlg As Integer '// 日付逆転フラグ（-1：日付が逆転、1：日付が通常） '// 引数を保持 sStartDate = a_sStartDate '// 開始日付 sEndDate = a_sEndDate '// 終了日付 '// 引数の開始日付または終了日付が日付として不正の場合 If IsDate(sStartDate) = False Or IsDate(sEndDate) = False Then '// 以降を処理せずNullを返す DateDifYM = Null Exit Function End If '// 開始日付の方が未来の場合 If sStartDate > sEndDate Then iReverseFlg = -1 Dim s As String '// 開始日付と終了日付を入れ替える s = sStartDate sStartDate = sEndDate sEndDate = s Else iReverseFlg = 1 End If '// 終了日付の翌日を取得 sEndNextDate = CStr(CDate(sEndDate) + 1) '// 開始日付と終了翌日日付の年、月、日をそれぞれ取得 iStartYear = Year(sStartDate) iStartMonth = Month(sStartDate) iStartDay = Day(sStartDate) iEndYear = Year(sEndNextDate) iEndMonth = Month(sEndNextDate) iEndDay = Day(sEndNextDate) '// ------------------------------------ '// 年数差の取得 '// ------------------------------------ '// 単純な年数差を取得（開始年と終了年は年数に含まない） iDiffYear = iEndYear - iStartYear - 1 '// 終了月の方が大きい場合 If (iStartMonth < iEndMonth) Then iDiffYear = iDiffYear + 1 '// 開始月と終了月が同じで開始日より終了日が大きい場合 ElseIf (iStartMonth = iEndMonth) And (iStartDay <= iEndDay) Then iDiffYear = iDiffYear + 1 End If '// ------------------------------------ '// 月数差の取得 '// ------------------------------------ '// 年が異なる場合 If iStartYear <> iEndYear Then '// 開始日付の翌月から12月までの月数を加算 iDiffMonth = iDiffMonth + (12 - iStartMonth) '// 終了日付の1月から前月までの月数を加算 iDiffMonth = iDiffMonth + (iEndMonth - 1) '// 終了日が開始日含め未来の場合 If (iEndDay >= iStartDay) Then iDiffMonth = iDiffMonth + 1 End If '// 月が異なる場合 ElseIf iStartMonth <> iEndMonth Then iDiffMonth = iEndMonth - iStartMonth - 1 '// 終了日が開始日含め未来の場合 If (iEndDay >= iStartDay) Then iDiffMonth = iDiffMonth + 1 End If End If '// 12か月を超える分は除外 iDiffMonth = iDiffMonth Mod 12 '// ------------------------------------ '// 日付が逆転している場合に正負を反転させる '// ------------------------------------ iDiffYear = iDiffYear * iReverseFlg '// 年数差 iDiffMonth = iDiffMonth * iReverseFlg '// 月数差 '// "x年yか月"を返す DateDifYM = iDiffYear & "年" & iDiffMonth & "か月" End Function |
使い方
VBAでの使い方
VBAで使う場合のサンプルです。引数に開始日付と終了日付を渡して、戻り値で「〇年〇か月」を受け取ります。
|
1 2 3 4 5 6 7 8 9 |
Sub DateDifYMTest() Dim s s = DateDifYM("2021/03/28", "2023/01/27") Debug.Print s s = DateDifYM(Range("A1"), Range("B1")) Debug.Print s End Sub |
シートでの使い方
この関数を勤続年数を表示したいブックか、Personal.xlsbに保存しておけば、勤続年数を表示したいセルに「=DateDifYM(A1,B1)」のように書くことでシート上でも勤続年数を表示することができます。
DATEDIF関数での勤続年数の算出は何が問題か？
先にも書きましたが、一般的には勤続年数などの日付の期間を求める場合にはDATEDIF関数が紹介されますが、いくつかの問題があります。
大別すると４つ挙げられます。
- 日付が逆転しているとエラーになる。
- うるう年の場合に正しく計算できない場合がある。
- 単位の６種類のうち４種類（”M”、”MD”、”YM”、”YD”）で正しく動作しない場合がある。
- そもそもDATEDIF関数は非推奨。（2002年が最終版のLotus 1-2-3との互換性のために残してある）
DATEDIF関数の使い方ですが、「=DATEDIF(開始日, 終了日, 単位)」の数式で扱います。開始日＞終了日の場合はエラー（#NUM!）になります。
「単位」には６つあります。
| 単位 | 戻り値 | 補足 |
|---|---|---|
| Y | 年数差 | 同年の場合は0が返る。 |
| M | 月数差 | 同月の場合は0が返る。月末日同士で、かつ、開始日の日＞終了日の日の場合に１か月ずれる。 |
| D | 日数差 | 同日の場合は0が返る。セルの引き算「=A1-B1」と同じ。 |
| MD | 年月を除いた日のみの日数差 | うるう年の場合に正しく動作しない場合がある。（2000/1/31～2000/3/1で-1など） |
| YM | 年と日を除いた月のみの月数差 | 月末日同士で、かつ、開始日の日＞終了日の日の場合に１か月ずれる。 |
| YD | 年を除いた月日での日数差 | うるう年を考慮せずに計算されるため、うるう年の場合は１日ずれる。 |
補足に書いた通りですが、いろいろと問題があります。既知の問題ですがずっと放置されているため、Microsoftが直すことはおそらくないでしょう。
勤続年数を求める場合は月末日になることも多いでしょうから、DATEDIF関数を使う場合は事前に問題がないか確認した方がいいと思われます。