EXCEL関数を使って週、月、年度の初日や最終日を取得する
エクセルでよく使われるデータの1つとして、日付データがあります。そのまま使うだけでなく、週や月、年度に合わせての変換が必要なこともあるでしょう。
そこで今回は、よく使われる日付の変換をエクセルで行う方法を紹介します。
日付を変換するためのエクセル関数例
今回紹介するエクセル関数の一覧です。エクセル関数をクリックすると解説に飛びます。
※表示例は日付を2022/1/20, 年度開始月を4月とした場合のものです
| 取得したい日付 | エクセル関数 | 表示例 |
|---|---|---|
| 月の初日 | 日付-DAY(日付)+1 | 2022/01/01 |
| 月の最終日 | EOMONTH(日付,0) | 2022/01/31 |
| 指定日直前の日曜日 | 日付-(WEEKDAY(日付,1)-1) | 2022/01/16 |
| 指定日直前の月曜日 | 日付-WEEKDAY(日付,3) | 2022/01/17 |
| 指定日直後の土曜日 | 日付-(WEEKDAY(日付,16)-1)+7 | 2022/01/22 |
| 指定日を含む年度の初日 | DATE(YEAR(EOMONTH(日付,-(年度開始月-1))),年度開始月,1) | 2021/04/01 |
| 指定日を含む年度の最終日 | EOMONTH(DATE(YEAR(EOMONTH(日付,-(年度開始月-1))),年度開始月,1),11) | 2022/03/31 |
ここからは、上記エクセル関数の解説をしていきます。
エクセル関数の解説
⓪エクセルにおける日付の管理方法
エクセルでの日付はシリアル値と呼ばれるもので管理されており、これは「1899/12/31からの経過日数」を示します。そのため、日付にプラス1することで翌日に、マイナス1することで前日にすることができます。
①月の初日：日付-DAY(日付)+1
DAY(日付)関数は、日付の日を取得するエクセル関数です。
元の日付からDAY(日付)の値をマイナスします。そのままだと前月の最終日になってしまうため、プラス1することで当月の1日を求めることができます。
②月の最終日：EOMONTH(日付,0)
EOMONTH(日付, 月)関数は、月の最終日を取得するエクセル関数です。
EOMONTH(日付, 月)関数の第1引数に日付、第2引数に0を設定することで当月の最終日を求めることができます。
③指定日直前の日曜日：日付-(WEEKDAY(日付,1)-1)
WEEKDAY(日付, 種類)関数は、日付の曜日を取得するエクセル関数です。
種類を1にすると、日曜日のときは1～土曜日のときは7が返ってきます。
元の日付からWEEKDAY(日付, 1)の値をマイナスします。そのままだと前の週の土曜日になってしまうため、プラス1することで目的の日付を求めることができます。
④ 指定日直前の月曜日：日付-WEEKDAY(日付,3)
WEEKDAY(日付, 種類)関数の種類を3にすると、月曜日のときは0～日曜日のときは6が返ってきます。
元の日付からWEEKDAY(日付, 3)の値をマイナスすることで目的の日付を求めることができます。
⑤ 指定日直後の土曜日：日付-(WEEKDAY(日付,16)-1)+7
WEEKDAY(日付, 種類)関数の種類を11～17にすると、それぞれ月～日から1～7となる値が返ってきます。
元の日付からWEEKDAY(日付, 種類)の値をマイナスします。そのままだと目的の曜日の前日になってしまうため、プラス1をします。直後の日付を求める場合は、さらにプラス7することで目的の日付を求めることができます。
⑥指定日を含む年度の初日：DATE(YEAR(EOMONTH(日付,-(年度開始月-1))),年度開始月,1)
年度は一般的には4月区切りですが、会社によってはそれ以外の区切りになっている場合もあります。そのため、どの月にも利用できるようなエクセル関数を紹介します。
DATE(年, 月, 日)関数は、指定した年月日から日付データを作成することができるエクセル関数です。
また、EOMONTH(日付, 月)関数の月に0以外の値を設定すると、その分だけ翌月または前月の最終日が返ってきます。
求め方としては、まず日付の月が年度最終月より小さいか確認する必要があります。小さい場合は翌年になっているため、その分戻す必要があります。
ここでは、EOMONTH(日付, 月)関数により日付から年度最終日の月だけ前の月を求め、その年を年度の年とします。そしてDATE(年, 月, 日)関数でその年、年度開始月、1日を指定することで年度開始日を求めることができます。
⑦指定日を含む年度の最終日：EOMONTH(DATE(YEAR(EOMONTH(日付,-(年度開始月-1))),年度開始月,1),11)
EOMONTH(日付, 月)関数の日付は⑥をそのまま使用しています。その11か月後の最終日を求めることで、年度の最終日を求めることができます。
まとめ
今回は、エクセルで日付を変換するいくつかの手法を紹介しました。
エクセル業務においては上記以外にも様々な日付の変換が求めらることがあると思いますが、今回紹介したエクセル関数や方法を応用して簡単に求められるようになりましょう。
