こんにちは！ くのーるです！
この記事では、Google スプレッドシート上で使える 日付関数 (TODAY, WEEKDAY, MONTH, EOMONTH, WORKDAY) を組み合わた、さまざまな条件の営業日算出レシピ をご紹介いたします！それではレッツゴー！ 🚀
できるようになること
「日付関数を組み合わせて営業日を算出」といっても、なかなかイメージしにくいですよね。
ということで、これからご紹介していく関数でどういったことができるのかを見てみましょう。
はじめにこちらをご覧ください (じゃじゃん！)
営業日の てんこ盛り ですね！ 笑
こちらの Google スプレッドシートは 2022/02/04 (金) を基準として、以下のような さまざまな条件の営業日を自動的に算出しています (F 列)。
- 毎日の営業日
- 月初の営業日
- 月末の営業日
- 月末 n 日前の営業日
- 毎月 n 日の営業日
- 毎月 n 日の営業日
- 毎週 d 曜日の営業日
- 毎年 M 月の ◯◯ の営業日
- M か月ごとの ◯◯ の営業日
ここでいう営業日とは、土日・祝日・会社指定休日を除く日付のことを指します
これは F 列に日付関連の関数を仕込んでおり、今日 (今週) を基準として 自動的に日付が更新される仕組み になっています。
「毎年 2 月かつ月初の営業日 (土日祝後倒し)」なんていう、複雑な条件の自動算出にも対応できます。
どんなときに役立つの？
指定した条件に一致する営業日を特定できるということは、Google スプレッドシート上で予定管理をおこなう際にとても重宝します。
例としては、以下のように Google スプレッドシートに予定タスクの棚卸しをおこない、E 列の日付部分に対して、上で紹介した営業日算出の関数を仕込んでおくと、予定日が自動更新されるタスク管理表がかんたんにできてしまいます。
これはとっても便利ですね！
日付関数の解説
さて上記のような各種営業日の算出には、Google スプレッドシートの TODAY, WEEKDAY, MONTH, EOMONTH, WORKDAY 関数 を駆使する必要があります。
まずはこれら各関数についての理解を深めていきましょう。
本記事に登場する日付関数について、一つずつ解説していきます。
参考元: Google ヘルプ_Google スプレッドシートの関数リスト
TODAY 関数
構文
TODAY()
解説
TODAY 関数は、現在の日付 を返すというシンプルな関数になっています。
「現在」については、スプレッドシートのタイムゾーン設定に依存している模様です。
日付を超えると、自動的に TODAY 関数の結果も翌日の値に変わるという性質があります。
使用例
現在 (2022/02/07) に TODAY 関数を実行することで、当日の日付が返ってきています。
WEEKDAY 関数
構文
WEEKDAY("日付", [種類])
解説
WEEKDAY 関数は、指定した日付に対応する 曜日の数値 を返します。
曜日と数値の関係は日曜日 = 1 から始まり、月曜日 = 2 ,…, 土曜日 = 7 となっています。
曜日と数値の関係は、以下のとおり[種類] で指定する数値型引数で設定ができます。
- 1 の場合 (デフォルト): 日曜日を 1 とする
- 2 の場合: 月曜日を 1 とする
- 3 の場合: 月曜日を 0 とする
使用例
2022/02/07 は月曜日なので、2 が返ってきます。
MONTH 関数
構文
MONTH("日付")
解説
MONTH 関数は、指定した日付に対応する 月の数値 を返します。
使用例
セル D1 にある 2022/02/01 を参照し、月数の 2 が返ります。
上の画像のように文字列と組み合わせると、動的なタイトル テキストを作るようなことができます。
EOMONTH 関数
構文
EOMONTH("起算日", 月数)
解説
EOMONTH 関数は End Of Month の略で、起算日から指定した月数の 月の末日 を返してくれる便利な関数です。
少しわかりづらいかもしれませんので、次の使用例をご覧ください。
使用例
上の画像例では、2022/02/07 のひと月後の末日を求めるという式になっており、2022/03/31 が返ってきています。
なお、数値型引数である 1 の部分は 負の整数にも対応 しており、=EOMONTH("2022/2/7", -1) とした場合は前月の末日である 2022/01/31 が返ってきます。
WORKDAY 関数
構文
WORKDAY("起算日", 経過日数, [休日])
解説
WORKDAY 関数は、起算日から指定した n 営業日後の日付 を求める関数です。
本関数における 経過日数 とは「土日、および引数として設定可能な [休日] を除外」したものであり、これを起算日に加算するという仕組みで n 営業日後の日付が返ってくるものとなっています。
一般的に営業日を求める際には、祝日と会社の指定休業日を除く ことが一般的ですので、本関数を利用する多くの場合、以下のような 除外リストのシートをあらかじめ用意する必要があります。
もし会社などで祝日リストが準備されていない場合は、Google スプレッドシートでマスターシートを作成しておくことをおすすめします。
また当ブログでは、Google Apps Script (GAS) をつかった 毎年自動で更新されるメンテナンス フリーな祝日リスト の作り方も紹介していますので、あわせて参考にしてみてください。
使用例
上記の画像例では、2022/02/07 (月) を起算日として、4 日後の営業日 を求めています。
詳しく見ていきましょう。
2022/02/07 (月) の 4 日後は、2022/02/11 (金) にあたりますが、この日は祝日「建国記念の日」に該当するため、引数でセットしている「祝日リスト」シートの定義により除外 となります。
くわえて WORKDAY 関数は土日を除く仕様につき、02/11 (金), 02/12 (土), 02/13 (日) の 3 日間がスキップ されます。
結果、2022/02/07 (月) の 4 日後の営業日は、正しく 2022/02/14 (月) と返ってきています。
日時関数の組み合わせレシピ一挙公開！
ここまでは日時関数の基本的な仕様や使い方をお伝えいたしました。
それでは本題の 「日時関数の組み合わせ」 によって実現できる さまざまな条件の営業日算出レシピ を公開いたします！
以下で紹介する式は、前述のとおり「祝日リスト」のシートが用意されていることを前提としたものになります。
毎日の営業日
式
土日祝前倒し Ver.
=WORKDAY(TODAY() + 1, -1, '祝日リスト'!B2:B)
土日祝後倒し Ver.
=WORKDAY(TODAY() - 1, 1, '祝日リスト'!B2:B)
結果
テストとして基準日の TODAY() 部分を、祝日の 2022/02/11 (金) に書き換えています。
土日祝前倒し・後倒しの処理が正しくおこなわれていることが確認できます。
解説
WORKDAY 関数の基本構文はWORKDAY("起算日", 経過日数, [休日]) です。
今回作成したい式は「毎日の営業日」であるため、単純に起算日 部分に TODAY 関数をセットし、経過日数を 0 としたいところですが、WORKDAY 関数の仕様上、必ず 0 を除く正または負の数 を設定しなくてはなりません。
そこで帳尻合わせ的に 起算日 を +1 しておいて、経過日数 を -1 とすることで解決しています。
なお、この関数が土日・祝日に実行された場合、返り値が 前倒しの営業日 になるか、後倒しの営業日 になるかは、以下のとおり経過日数 の正負によって決まります。
正の数を設定した場合: 未来の方向に日付が倒れます
負の数を設定した場合: 過去の方向に日付が倒れます
月初の営業日 (土日祝後倒し)
式
=WORKDAY(EOMONTH(TODAY(), -1), 1, '祝日リスト'!B2:B)
結果
テストとして基準日の TODAY() 部分を、2022/01/10 (月) に書き換えています。
01/01 ~ 01/04 は会社指定休業日 (年末年始休暇) に該当するため、後倒れとなり 2022/01/05 (水) が返ります。
解説
EOMONTH(TODAY(), -1) の部分から見ていきましょう。
EOMONTH 関数ははじめに説明のとおり、指定された 月の末日 を返します。
上の式では、第一引数である対象月に対して、第二引数で-1 が設定されているため、前月の末日 が返ってきます (今回の例では 2021/12/31)。
これを WORKDAY 関数WORKDAY("起算日", 経過日数, [休日]) の起算日 にセットし、くわえて経過日数 の引数として 1 を与えます。
これにより 前月の末日の翌日営業日、つまり 月初の営業日 (土日祝後倒し) を求めるという式になります。
月末の営業日 (土日祝前倒し)
式
=WORKDAY(EOMONTH(TODAY(), 0) + 1, -1, '祝日リスト'!B2:B)
結果
テストとして基準日の TODAY() 部分を、2022/04/01 (月) に書き換えています。
月末の 2022/04/30 は土曜日のため、前倒しになります。
また 2022/04/29 (金) は祝日のため、さらに前に倒れ 2022/04/28 (木) が月末営業日として返ります。
解説
EOMONTH(TODAY(), 0) の部分から見ていきましょう。
EOMONTH 関数は、WORKDAY 関数とは異なり、第二引数に 0 を設定することができます。
これにより第一引数で指定された月の末日を返します (今回の例では 2022/04/30)
これを WORKDAY 関数WORKDAY("起算日", 経過日数, [休日])) の起算日 にセットすると =WORKDAY(EOMONTH(TODAY(), 0), 経過日数, '祝日リスト'!B2:B) のような形になります。
あとは前述の解説 毎日の営業日 と同じ要領になります。
WORKDAY 関数には 0 をもたせられない こと、また今回は 土日祝前倒しとしたい ことから、=WORKDAY(EOMONTH(TODAY(), 0) + 1, -1, '祝日リスト'!B2:B) となります。
月末 n 日前の営業日
式
土日祝前倒し Ver.
=WORKDAY(EOMONTH(TODAY(), 0) + 1 - n, -1, '祝日リスト'!B2:B)
土日祝後倒し Ver.
=WORKDAY(EOMONTH(TODAY(), 0) - 1 - n, 1, '祝日リスト'!B2:B)
結果
テストとして基準日の TODAY() 部分を、2022/02/01 (火) に書き換えています。
また営業日 n 日前の n 部分については、本シートの D 列参照値_n で定義しており、上記の例では 月末 1 日前 としています。
2022/02 の月末は 2022/02/28 (月) となっており、その 1 日前は 2022/02/27 (日) です。
よって 土日祝前倒しは 2022/02/25 (金)、後倒しは 2022/02/28 (月) と正しい結果が返ってきていることがわかります。
解説
この式は - n の部分で、「n 日前」の調整をくわえているだけで、理屈は 月末の営業日 (土日祝前倒し) と同じものになります。
毎月 n 日の営業日
式
土日祝前倒し Ver.
=WORKDAY(EOMONTH(TODAY(), -1) + 1 + n, -1, '祝日リスト'!B2:B)
土日祝後倒し Ver.
=WORKDAY(EOMONTH(TODAY(), -1) - 1 + n, 1, '祝日リスト'!B2:B)
結果
今回はテストとして基準日の TODAY() 部分を、2022/02/01 (火) に書き換えています。
また毎月 n 日の n 部分については、本シートの D 列参照値_n で定義しており、上記の例では 12 日 としています。
2022/02/12 は土曜日、2022/02/11 (金) は祝日です。
よって 土日祝前倒しは 2022/02/10 (木)、後倒しは 2022/02/14 (月) と正しい結果が返ってきていることがわかります。
解説
土日祝前倒し Ver. の EOMONTH(TODAY(), -1) + 1 + n 部分から見ていきましょう。
ひとまず、+1 はおいておきまして、EOMONTH(TODAY(), -1) + n に着目します。
EOMONTH(TODAY(), -1) の部分は、EOMONTH 関数 で解説のとおり、本日を起点とした、前月末の日付をあらわします。
これに指定する月の日にちn を加算することで、その月の日にちを求めています。
これを WORKDAY 関数WORKDAY("起算日", 経過日数, [休日])) の起算日 にセットすると =WORKDAY(EOMONTH(TODAY(), -1) + n, 経過日数, '祝日リスト'!B2:B) のような形になります。
最後に 経過日数 の設定になりますが、これも 毎日の営業日 で解説した内容とまったく同じ理由で、0 を設定することができないことから、-1 を与え、帳尻合わせ的に EOMONTH 関数部分には 1 を加えることで、最終的に=WORKDAY(EOMONTH(TODAY(), -1) + 1 + n, -1, '祝日リスト'!B2:B) という式になります。
なお土日祝後倒し Ver. については、経過日数 を正の数にすればよいので、帳尻合わせ部分の正負を逆転させて =WORKDAY(EOMONTH(TODAY(), -1) - 1 + n, 1, '祝日リスト'!B2:B) とします。
毎週 d 曜日の営業日
式
土日祝前倒し Ver.
=WORKDAY(TODAY() - WEEKDAY(TODAY()) + d + 1, -1, '祝日リスト'!B2:B)
土日祝後倒し Ver.
=WORKDAY(TODAY() - WEEKDAY(TODAY()) + d - 1, 1, '祝日リスト'!B2:B)
結果
テストとして基準日の TODAY() 部分を、2022/02/07 (月) に書き換えています。
また毎週 d 曜日のd 部分については、本シートの C 列参照値_d で定義しており、上記の例では 6 としています。
この式における曜日と数値の関係は 1: 日, 2: 月, …, 7: 土 としており、ここでは 毎週金曜日 を指定していることになっています。
2022/02/11 (金) は祝日につき、土日祝前倒しは 2022/02/10 (木)、後倒しは 2022/02/14 (月) と正しい結果が返ってきていることがわかります。
解説
土日祝前倒し Ver. の TODAY() - WEEKDAY(TODAY()) + d + 1 部分から見ていきましょう。
まずは肝となる TODAY() - WEEKDAY(TODAY()) という部分に着目します。
これは式のとおり、本日の日付を、本日の日付の曜日数で減算 しています。
WEEKDAY 関数における曜日と数値の対応は、WEEKDAY 関数の解説 のとおり 1: 日, 2: 月, …, 7: 土 です。
つまり、この式がなにをあらわしているかというと、いつでも先週の土曜日の日付 が返ってくるというものになっています。
ということは、これに d 日を加えることで、特定の曜日を求められることがわかりますね。
つまり、TODAY() - WEEKDAY(TODAY()) + d の d に対して、1 が入れば日曜日、2 が入れば月曜日といった具合です。
これを WORKDAY 関数WORKDAY("起算日", 経過日数, [休日])) の起算日 にセットすると =WORKDAY(TODAY() - WEEKDAY(TODAY()) + d, 経過日数, '祝日リスト'!B2:B) のような形になります。
この形になれば、これまで同様経過日数 に -1 を与え、帳尻合わせ的に第一引数部分に 1 を加えれば、完成です。
=WORKDAY(TODAY() - WEEKDAY(TODAY()) + d + 1, -1, '祝日リスト'!B2:B)
土日祝後倒しにしたい場合は、こちらも同様に、帳尻合わせ部分の正負を逆転させるだけで対応可能です。
=WORKDAY(TODAY() - WEEKDAY(TODAY()) + d - 1, 1, '祝日リスト'!B2:B)
毎年 M 月の ◯◯ の営業日
式
基本
=IF(MONTH(TODAY()) = M, これまでに紹介した関数, "設定済み")
例: 毎年 M 月の月初の営業日 (土日祝後倒し)
=IF(MONTH(TODAY()) = M, WORKDAY(EOMONTH(TODAY(), -1), 1, '祝日リスト'!B2:B), "設定済み")
結果
上の例では、毎年 01 月の月初の営業日 (土日祝後倒し) を求めています。
今回はテストとして基準日の TODAY() 部分を、2022/01/07 (月) に書き換えています。
毎年 M 月のM 部分については、本シートの E 列参照値_M で定義しており、上記の例では 1 月 としています。
01/01 ~ 01/04 は会社指定休業日 (年末年始休暇) に該当するため、後倒れとなり 2022/01/05 (水) が正しく返ってきています。
なお 01 月以外の月の場合は、設定済み という文字列が返ってきます。
解説
毎年 01 月の月初の営業日 (土日祝後倒し) というと、一見複雑にみえる条件ですが、これまでに紹介してきた関数を組み合わせるだけで、かんたんに対応できます。
ここまでの解説を読まれている場合、上の式は =IF(MONTH(TODAY()) = M, の部分のみ理解すれば OK です。
MONTH 関数は第一引数の 日付の月 を返します。
よってこれが指定月M に該当するかどうか IF 関数で判定します。
あとは、これまでに紹介してきた任意の営業日算出の関数を、IF の True 判定 (第二引数) にセットするだけです。
なお False 判定 (第三引数) に関しては便宜上、設定済み という文字列を返していますが、任意の設定で構いません。
M か月ごとの ◯◯ の営業日
式
基本
=IF(MOD(MONTH(TODAY()), M) = n, これまでに紹介した関数, "設定済み")
例: M か月ごとの月末の営業日 (土日祝前倒し)
=IF(MOD(MONTH(TODAY()), M) = n, WORKDAY(EOMONTH(TODAY(), 0) + 1, -1, '祝日リスト'!B2:B), "設定済み")
結果
上の例では、02 か月ごとの月末の営業日 (土日祝前倒し) を求めています。
今回はテストとして基準日の TODAY() 部分を、2022/01/07 (木) に書き換えています。
M か月のM 部分については、本シートの E 列参照値_M で定義しており、上記の例では 2 か月ごと としています。
月末の 2022/04/30 は土曜日のため、前倒しになります。
また 2022/04/29 (金) は祝日のため、さらに前に倒れ 2022/04/28 (木) が月末営業日として返ります。
なお奇数月 (1, 3, 5, 7, 9, 11 月) の場合は、設定済み という文字列が返ってきます。
解説
こちらも一見難しそうにみえますが、毎年 M 月の ◯◯ の営業日 同様、これまでの関数を組み合わせて、かんたんにつくれるものとなっています。
ここまでの解説を読まれている場合は、 =IF(MOD(MONTH(TODAY()), M) = n の部分のみ理解すれば OK です。
MOD 関数は、第一引数を第二引数 M の数値で割った 余りを算出 する関数です。
この式の n は、余りの数をあらわしています。
よって以下の例のようにM と n の組み合わせで IF 判定をおこなうことにより、任意の ◯ か月ごと の設定を可能としています。
| M の値 | N の値 | 結果 |
| 2 | 0 | 2 か月ごと (2, 4, 6, 8, 10, 12 月) |
| 2 | 1 | 1 月から始まり 2 か月ごと (1, 3, 5, 7, 9, 11 月) |
| 3 | 0 | 3 か月ごと (3, 6, 9, 12 月) |
| 3 | 2 | 2 月から始まり 3 か月ごと (2, 5, 8, 11 月) |
あとは、これまでに紹介してきた任意の営業日算出の関数を、IF の True 判定 (第二引数) にセットするだけです。
なお False 判定 (第三引数) に関しては便宜上、設定済み という文字列を返していますが、任意の設定で構いません。
日時関数チートシート
本記事で紹介した各種レシピのチートシートは以下のとおりです。コピペで使っちゃってください。
| 種別 | 関数 | d | n | M | 結果 (起算日: 2022/02/15 (火)) | 備考 |
|---|---|---|---|---|---|---|
| 毎日の営業日 (土日祝前倒し) | =WORKDAY(TODAY() + 1, -1, ‘祝日リスト’!B2:B) | – | – | – | 2022/02/15 (火) | |
| 毎日の営業日 (土日祝後倒し) | =WORKDAY(TODAY() – 1, 1, ‘祝日リスト’!B2:B) | – | – | – | 2022/02/15 (火) | |
| 月初の営業日 (土日祝後倒し) | =WORKDAY(EOMONTH(TODAY(), -1), 1, ‘祝日リスト’!B2:B) | – | – | – | 2022/02/01 (火) | |
| 月末の営業日 (土日祝前倒し) | =WORKDAY(EOMONTH(TODAY(), 0) + 1, -1, ‘祝日リスト’!B2:B) | – | – | – | 2022/02/28 (月) | |
| 月末 n 日前の営業日 (土日祝前倒し) | =WORKDAY(EOMONTH(TODAY(), 0) + 1 – n, – 1, ‘祝日リスト’!B2:B) | – | 1 | – | 2022/02/25 (金) | |
| 月末 n 日前の営業日 (土日祝後倒し) | =WORKDAY(EOMONTH(TODAY(), 0) – 1 – n, 1, ‘祝日リスト’!B2:B) | – | 1 | – | 2022/02/28 (月) | |
| 毎月 n 日の営業日 (土日祝前倒し) | =WORKDAY(EOMONTH(TODAY(), – 1) + 1 + n, – 1, ‘祝日リスト’!B2:B) | – | 11 | – | 2022/02/10 (木) | |
| 毎月 n 日の営業日 (土日祝後倒し) | =WORKDAY(EOMONTH(TODAY(), – 1) – 1 + n, 1, ‘祝日リスト’!B2:B) | – | 11 | – | 2022/02/14 (月) | |
| 毎週 d 曜日の営業日 (土日祝前倒し) | =WORKDAY(TODAY() – WEEKDAY(TODAY()) + d + 1, -1, ‘祝日リスト’!B2:B) | 6 | – | – | 2022/02/18 (金) | 例:金曜日 (1: 日, 2: 月, …, 7: 土) |
| 毎週 d 曜日の営業日 (土日祝後倒し) | =WORKDAY(TODAY() – WEEKDAY(TODAY()) + d – 1, 1, ‘祝日リスト’!B2:B) | 6 | – | – | 2022/02/18 (金) | 例:金曜日 (1: 日, 2: 月, …, 7: 土) |
| 毎年 M 月の ~ に | =IF(MONTH(TODAY()) = M, 上記の関数いずれか, “設定済み”) | – | – | 2 | 2022/02/28 (月) | 例: 毎年 2 月の月末の営業日 (土日祝前倒し) |
| M か月ごとの ~ に | =IF(MOD(MONTH(TODAY()), M) = n, 上記の関数いずれか, “設定済み”) | – | 0 | 3 | 設定済み | 例: M = 3, n = 0 とした場合は 3, 6, 9, 12 月となる |
まとめ
Google スプレッドシートで使える日付関連のレシピを一挙公開いたしました！
上記で公開している「チートシート」は関数部分をコピペするだけで使えるので、営業日を算出する際にはぜひご利用ください。
ご紹介した日付関数は、一度セルに仕込んでおけば設定したサイクルにしたがって 自動的に値が更新される ため、大変便利かと思います。
今回は「営業日の算出」をメインに紹介しましたが、これらの関数を使いこなせると 自分の思い通りに日付操作ができるようになります！
さて次回の記事では、今回の営業日算出レシピをつかったスケジュール管理表をもとに、GAS (Google Apps Script) をつかった「予定日時になったらチャットツール「Slack」や「Discord」にリマインダー (通知メッセージ) を飛ばす」 という、さらに踏み込んだ、イチオシのレシピ をご紹介したいと思います。
乞うご期待ください 🚀
