- Home »
- エクセル関数の技 »
- 日付に関する技/月末など指定日を求めたい
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
更新:2025年4月3日;作成:2009年5月28日
DATE関数を使って月末の日を求める
- Excelでは日付はシリアル値という値で関連付けられています。
- 「月末とは次の月の1日の前日である」と考えれば容易な式で求めることができます。
- 例えば、2025年1月末日なら、2025年2月1日の前の日と考えます。
DATE関数を使って =DATE(2025,2,1)-1 → =DATE(2025,2,0)と書くことができます。
- A列の日付の月末を求める例
年はYEAR関数、月はMONTH関数で求め「+1」して次の月とします。日にちは前日なので「0」とします。
Excel for Microsoft365,Excel2021以降のSpillが利用できる場合は
=DATE(YEAR(A1:A8),MONTH(A1:A8)+1,0) と入力します。
Excel2019以前は =DATE(YEAR(A1),MONTH(A1)+1,0) と入力して、下方向へ数式をコピーします。
EOMONTH関数を使って月末の日を求める
- Excel2003以前では、この関数を使うには分析ツールのアドインを有効にしておく必要があります。
[ツール]→[アドイン]を選択し、[アドイン]ダイアログボックスの「分析ツール」にチェックを入れます。
- EOMONTH関数の構文 :=EOMONTH(開始日,月)
開始日から起算した月数を指定します。月に正の数を指定すると、起算日より後の日付を返し、負の数を指定すると、起算日より前の日付を返します。
EOMONTH関数の詳細な使い方は 月末の日付を返す(EOMONTH) をご覧ください。
- 当月末日は=EOMONTH("2025年2月10日",0) とか 先月末日は=EOMONTH("2025年3月10日",-1) と日付はダブルクォーテーションでくくります。
セルに入力された値の場合は =EOMONTH("2025年2月10日",A1) とします。
- A列の日付の月末を求める例
専用の関数なので、シンプルな式になります。ポイントは「月」の指定の仕方でしょう。
この関数はSpillに対応していないので、C1セルに =EOMONTH(A1,0) と入力して、下方向へ数式をコピーします。
月末が土日であったら前営業日を求める
- 次の項で説明するWORKDAY関数を利用します。ここでは結果を示します。
WORKDAY関数の構文:=WORKDAY(開始日,日数,[祭日])
詳細は 稼働日を求める(WORKDAY・WORKDAY.INTL関数の使い方) をご覧ください。
- ここでは、2025年8月5日 の月末を求めます。
ただし、月末が土日であったらその前日を求めます。
これを求めるには翌月の1日から前方向へ営業日を探します。2009年5月31日は日曜日ですので5月末の営業日は2009年5月29日となります。
翌月の1日は EOMONTH(A2,0)+1 で求めます。2025年9月1日 となります。
この日から前方向の営業日(土日を除く)には
=WORKDAY(EOMONTH(A2,0)+1,-1) とします。
2025年8月29日(金)を求めることができます。
月末が祝祭日であったら前営業日を求める
- このような月末はないだろう・・・と思いましたが、2007年4月29日が昭和の日で日曜日にあたり、翌30日は振り替え休日でした。
該当する日を調べてみると2029年4月29日、2035年4月29日が日曜日になります。祝日に変更がなければ、翌30日は振り替え休日となります。
- WORKDAY関数を利用します。また、祝日のリストが必要になります。
なお、祝日のリスト範囲には「祝日」と名前を定義しています。
- 土日祝日を除いた翌営業日は=WORKDAY(EOMONTH(A2,0)+1,-1,祝日) で求ることができます。
(追記) (追記ここまで)
- 月の締日が20日でこの日を超えると翌月の20日が締日となるとします。
- 日付が20日以前であれば「その月の20日」が締日で、20日を超えると「翌月の20日」が締日なので、IF関数で場合分けすれば良さそうです。
- =IF(DAY(A1)<=20,DATE(YEAR(A1),MONTH(A1),20),DATE(YEAR(A1),MONTH(A1)+1,20))
- 条件を「DAY(A1)>20」を満たすか否かとすれば、満たす時は[True、1」となり、満たさないときは「False、0」となるので
=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>20),20) とすることができます。
- 結果が0かもしくは1を足すのですから、この条件に合う論理式を入れればよいということです。
1となるのは日が20を超えたときですから(DAY(A1)>20)というのが考えられます。超えなかったらFalseで0となり都合がよいわけです。
土日であったら翌営業日としたい
- 次の項で説明するWORKDAY関数を利用します。ここでは結果を示します。
- C2:C5セルには20日締めの
C2セル =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>20),20)
- 例えば下図のように、2025年4月20日は日曜日なので、土日を除いた翌営業日は2025年4月21日となります。
2025年9月20日は土曜日なので、土日を除いた翌営業日は2025年9月22日とします。
土日を除いた翌営業日は=WORKDAY(A2-1,1) で求めます。締日の前日から数えて次の営業日を求めます。
これに、締日の計算式を入れこむと、
=WORKDAY(DATE(YEAR(A2:A5),MONTH(A2:A5)+(DAY(A2:A5)>20),20)-1,1) となります
土日祝祭日であったら翌営業日としたい
- 次の項で説明するWORKDAY関数を利用します。また、祝日のリストが必要になります。
なお、祝日のリスト範囲には「祝日」と名前を定義しています。
土日祝日を除いた翌営業日は=WORKDAY(B2-1,1,祝日) で求めます。締日の前日から数えて次の営業日を求めます。
これに、締日の計算式を入れこむと、=WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>20),20)-1,1,祝日) となります。
2025年7月21日は海の日で祝日になりますので、E3セルはその翌日 2025年7月22日 となります。
- ある指定日から、土日を除いた5日後の営業日(平日)の日付を求めたいとします。
- WORKDAY関数が利用できます。
- Excel2003以前では、この関数を使うには分析ツールのアドインを有効にしておく必要があります。
[ツール]→[アドイン]を選択し、[アドイン]ダイアログボックスの「分析ツール」にチェックを入れます。
- =WORKDAY(開始日,日数,祭日)
たとえば、2025年4月28日から5日後の日付を求めます。
カレンダーはこのようになっています。
5日後の土日を除いた営業日を求める
- 上の表の配置でしたら、=WORKDAY(B6,5) となります。
日付を指定するときは =WORKDAY("2025年4月28日",5) とか =WORKDAY(DATE(2025,4,28),5) とします。
これで、「2025年5月5日」が求められます。
5日後の土日祝祭日を除いた営業日を求める
- なお、祝日のリスト範囲には「祝日」と名前を定義しています。
- =WORKDAY(B6,5,祝日) とすると計算できます。
日付を指定するときは =WORKDAY("2025年4月28日",5,祝日) とか =WORKDAY(DATE(2025,4,28),5,祝日) とします。
これで、「2025年5月11日」が求められます。
(追記) (追記ここまで)
- ある期間から、土日を除いた営業日(平日)の日数を求めたいとします。
- =NETWORKDAYS(開始日,終了日,祭日) を使います。
- Excel2003以前では、この関数を使うには分析ツールのアドインを有効にしておく必要があります。
[ツール]→[アドイン]を選択し、[アドイン]ダイアログボックスの「分析ツール」にチェックを入れます。
詳細な使い方は 稼動日数を求める(NETWORKDAYS関数・NETWORKDAYS.INTL関数) をご覧ください。 - たとえば、2025年4月28日から2025年5月13日の営業日数を求めます。
カレンダーはこのようになっています。
土日を除いた営業日数を求める
- 上の表の配置でしたら、=NETWORKDAYS(B6,B21) となります。
日付を指定するときは =NETWORKDAYS("2025年4月28日","2025年5月13日") とか =NETWORKDAYS(DATE(2025,4,28),DATE(2025,5,13)) とします。
これで、「12」が求められます。
土日祝日を除いた営業日数を求める
- 祝日はリストを準備します。
なお、祝日のリスト範囲には「祝日」と名前を定義しています。 - =NETWORKDAYS(B6,B21,祝日) とすると計算できます。
日付を指定するときは =NETWORKDAYS("2025年4月28日","2025年5月13日",祝日) とか =NETWORKDAYS(DATE(2025,4,28),DATE(2025,5,13),祝日) とします。
これで、「9」が求められます。
- 「成人の日」「海の日」「敬老の日」「体育の日」は第2月曜日、第3月曜日の祝日を計算で求めたいというケースがあります。
- 日曜日始まり〜土曜日始まりのカレンダーを作成してみます。
- 月の初め「1日」の曜日のWEEKDAY関数の返り値と第2月曜日、第3月曜日の関係は下図のようになります。
- 2025年10月の体育の日(第2月曜日)は=CHOOSE(WEEKDAY(A1),9,8,14,13,12,11,10)) で求めることができます。
- 2009年9月の敬老の日(第3月曜日)は第2月曜日+7となりますので、=CHOOSE(WEEKDAY(A1),9,8,14,13,12,11,10)+7) で求めることができます。
- 上の関係をWEEKDAY関数の種類を3にすると
更に、この関係の曜日が1つ右にずれて↓のようになれば都合がよいのですが・・・
つまり、「1日」の前の日の曜日を使えば都合がよくなります。
- 1日の前の日はDATE(年,月,0)となりますので、
- 第2月曜日は=14-WEEKDAY(DATE(YEAR(A1),MONTH(A1),0),3) とすることができます。
- 第3月曜日は+7すればよいので、=21-WEEKDAY(DATE(YEAR(A1),MONTH(A1),0),3) とすることができます。
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
Home »
エクセル関数の技 »
日付に関する技/月末など指定日を求めたい
PageViewCounter
Counter
Since2006/2/27