Office Hack

エクセルの日付の様々な計算方法

  • Release
  • Update

エクセルでは日付の計算をする場面が多いと思います。

この記事では、月の計算、年の計算、日数の計算、営業日の計算方法をまとめました。

日付の「月」の計算方法

月初の計算

TODAY関数の入力

図のように、左から「1ヶ月前の月初」「当月の月初」「本日」「1ヶ月後の月初」のセルがあります。「本日」はTODAY関数で求めてます。

この当日を参照セルにして、それぞれの月初日を求めていきます。

当月の月初日を計算

当月の月初を求めるには、『=DATE(YEAR(D3),MONTH(D3),1)』となります。DATE関数で、年と月と日を指定しており、サイトの「日」の箇所を「1」にすることで月初にします。

1ヶ月前の月初日を計算

1ヶ月前の月初にするには数式をそのまま流用し、変更する部分は『MONTH(D3)-1』の部分で、1月から「-1」することで1月から12月に変更しております。

年も前年の年(2019年)に変更されました。

1ヶ月後の月初日を計算

1ヶ月後の月初にするには数式をそのまま流用し、変更する部分は『MONTH(D3)+1』の部分で、1月から「+1」することで1月から2月に変更しております。

月末の計算

当月の月末日を計算

本日の日付から「1ヶ月前の月末」「当月の月末」「1ヶ月後の月末」を求めていきます。

当月の月末を求めるには『=EOMONTH(D3,0)』で計算できます。第2引数の「0」は、当月という意味です。

1ヶ月前の月末日を計算

1ヶ月前の月末にするには数式をそのまま流用し、変更する部分は第2引数の「0」の部分で、こちらを「-1」に変更すると前月になります。

1ヶ月後の月末日を計算

1ヶ月後の月末にするには、変更する部分は第2引数を「1」に変更すると来月になります。

日付間の月数の計算

日付間の月数計算

起点日から何ヶ月経過したかを計算するには、DATEDIF関数を使います。

『=DATEDIF($B$3,C3,"m")』と入力しますが、第3引数の『"m"』は、「月」の単位となります。

他のセルへ反映

他のセルも計算します。すべて正しい経過月数が出力されました。

日付の「年」の計算方法

1年前と1年後の計算

1年前の計算

1年前や1年後の計算をするには「EDATE関数」を利用します。

1年前を表示するには『=EDATE(C3,-12)』と入力しますが、第2引数「-12」は月を指定するので、1年前にするならば-12ヶ月として計算します。

1年後の計算

1年後を表示するには『=EDATE(C3,12)』と入力します。

日付の「日数」の期間の計算方法

「1/1から1/5までは4日間」と、日付の期間を求めるにはDAYS関数を使います。

詳しくは以下の記事で説明しました。

日付の「営業日」の計算方法

営業日ベースでの「期日」を求める

納期を算出する表を作る

本日から○営業日後の納期を求める計算をします。

営業日ベースで計算するには、土日を省かないとなりません。さらに祝日も省くために、別途祝日の表を設ける必要があります。

WORKDAY関数の入力

納期の計算には「WORKDAY関数」を用います。WORKDAY関数は、土日を省いて計算してくれる機能が備わっているのと、祝日の日付を指定すれば祝日を省いて計算できます。

『=WORKDAY(』で数式を開始します。

開始日の参照

まずは「開始日」を指定します。開始日はTODAY関数を用いて本日の日付を参照しましょう。セルを固定するために$を利用します。

日数の参照

次に「日数」を指定します。ここでは営業日日数を参照します。

祭日の参照

最後に「祭日」を指定します。別途用意しました祝日の表の日付を参照します。セルを固定するために$を利用します。

納期の算出

本日より5日後の納期が算出されました。もし表示結果がシリアル値で表示された場合、赤矢印の箇所を日付に変更しましょう。

他のセルへの反映

他のセルへ反映させます。納品物Cの納期は2020/2/12となっており、間に1日だけ祝日があるのですが、しっかりと祝日が除外されております。

「期日までの営業日数」を求める

営業日日数を算出する表を作る

先ほどの表から、営業日日数を納期を入れ替えました。ここでは、納期までの営業日日数を求める計算をします。

NETWORKDAYS関数の入力

営業日日数の計算には「NETWORKDAYS関数」を用います。NETWORKDAYS関数もWORKDAYS関数と同様、土日を省いて計算してくれる機能が備わっているのと、祝日の日付を指定すれば祝日を省いて計算できます。

『=NETWORKDAYS(』で数式を開始します。

開始日の参照

まずは「開始日」を指定します。セルを固定するために$を利用します。

終了日の参照

次に「終了日」を指定します。ここでは納期を参照します。

祭日の参照

最後に「祭日」を指定します。別途用意しました祝日の表の日付を参照します。セルを固定するために$を利用します。

営業日日数の算出

営業日日数が算出されました。WORKDAY関数の表では、営業日日数が「5」であったのに対し、NETWORKDAYS関数では「6」と計算されました。

NETWORKDAYS関数は、本日も含めた計算となりますので+1となります。

他のセルへ反映

他のセルへ反映させます。納品物Cの納期は2020/2/12となっており、間に1日だけ祝日があるのですが、しっかりと祝日が除外されております。

おすすめの商品をご紹介

その他、様々な日付の編集方法







よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top