スプレッドシートで自動更新のカレンダーを作った話

リリース日やQA開始日のような特定の日を、カレンダー表記と共に確認したいことってないですか？
自分はあります。
なのでまずはスプレッドシートでカレンダーを作ってみました。

要件定義

ちょっと複雑なことをやろうとしているので、まずは要件を定義してみたいと思います。

• 毎日自動で更新されること
• 土日祝が通常の紙のカレンダーのような表記で分かりやすいこと
• 前月・来月部分の日付はグレー背景になっていること

完成品のイメージ

これが自動更新されると最高なわけですね。どうやって実現するかを書いていきます。

作り方

今年の年や今月の月を表示している部分

ここは簡単ですね。

=year(today())

=month(today())

これだけで実現できます。

今月のカレンダー左上の日付部分

日付の式

ここは少し難しいです。
カレンダーの一番最初の日付（左上の部分）の式は次の通りです。

=date(B2,B3,1) - weekday(date(B2,B3,1)) + 1

なお、ここでいうB列は月を表示している部分になります。（A列は見やすさのためマージンとして利用しています）
一つ一つ分解して見ていきましょう。
date 関数は年、月、日を個別に指定すると、日付に変換してくれる関数になります。
ここでいう B2 は year(today()) 、つまり今年を表し、B3 は month(today()) 、つまり今月を表します。
なので、ここで使っている date(B2,B3,1) は今月の1日の日付を表すことになります。

もう一つのweekday 関数は指定した日付に対応する曜日を数値で返してくれる関数になります。
デフォルトでは日曜日が1、土曜日が7になります。
中に入れているのは先ほどの date(B2,B3,1) なので、今月の1日の曜日を取ってきています。

これらの要素を元にこの式を平たくいえば、以下のようになります。

=date(B2,B3,1) - weekday(date(B2,B3,1)) + 1
="今月1日の日付" - "今月1日の曜日（日曜なら1、土曜なら7）" + 1

これがどうしてカレンダーの左上になるか、具体的な例を元に説明しましょう。
2020年6月1日は月曜日です。なので、2020年6月のカレンダーの左上の日付は、カレンダー上は2020年5月31日（日）になります。
これはつまり、月の始めの日（2020年6月1日）の曜日が月曜なら、カレンダーの左上は1日引いた日付になるということです。
同じように、月の始めの日（？月1日）の曜日が火曜日なら、その月のカレンダー左上は2日引いた日付になります。
ということは、ある月のカレンダー左上を計算したかったら、ある月の1日（？月1日）から、曜日の分（日曜＝0、月曜＝1、火曜＝2...土曜＝6）を引いてあげれば良さそうです。
これを式にしたものが、上記の式になります。

日付の表示

カレンダー左上の日付を計算することはできました。
しかし、そのまま表示すると、年月日の表示になってしまいます。
一方で、だからといって year や month などで年や月を取ってきたように、日だけを取ってしまうと、年月日のデータでは無くなってしまいます。
表示上はそれでも良いかもしれませんが、その日が祝日なら赤文字にする、のような表示仕様を満たすのはちょっと面倒になってしまいます。
なるべく中身は実際は年月日だけど、日付だけを表示したい。
そんな時は、表示形式をいじって日付だけを表示するようにすれば実現できます。
スプレッドシートでは表示形式＞数字＞表示形式の詳細設定＞その他の日付や時刻の形式から、以下のように日付だけの表示に変更することができます。

今月のカレンダーのその他の日付部分

カレンダー左上の日付が計算できればあとは簡単です。なんといっても、表示上は日だけですが、中身は年月日です。
カレンダー左上から順番に、 +1 した日付を計算していくだけで、全ての日付を計算できます。
カレンダー表示は基本的に最大6週なので、6週分+1していくと今月のカレンダー表示になります。

来月の表示部分

次の月の計算は簡単です。なんといっても今月の表示ができています。
まずは月の表示ですが、こちらは単に今月カレンダーの右下が必ず来月になるので、その月を month で取ってくるだけでいいでしょう。
日付の部分も、今月と基本的には同じです。
来月カレンダーの左上は、以下の式で実現できます。

=date(*,*,1) - weekday(date(*,*,1)) + 1
="来月1日の日付" - "来月1日の曜日（日曜なら1、土曜なら7）" + 1

年は来年になっている可能性があるので、今月カレンダーの右下の日付から year 関数で年を取ってくるといいでしょう。
月は先ほど計算した月がありますね。
これらを入れてあげると、来月のカレンダーはもう出来上がりです。

土日祝や前月・来月部分のグレー表示などの部分

これらのカレンダーっぽい見た目の実現は基本的には全て表示形式＞条件付き書式で実現できます。
ただし土日は固定なので、背景色を固定で指定しています。

祝日は条件付き書式で、該当のセルの日付が祝日と一致したときに赤文字になるようにしています。
祝日は別の列やシートに記載しておき、それを参照するといいでしょう。
この祝日リストは手運用だと大変なので、別の記事でも記載したようにGoogleカレンダーの祝日カレンダーを参照して自動で取ってくるようにすると便利だと思います。

残りの前月・来月部分のグレー表示も同じく条件付き書式でグレーにしています。
式はこんな感じ。

=month(D4)<>$B$3

D4は対象の日付のあるセル、B3は今月の月です。
読み解くと簡単で、monthで取ってきた月が今月の月の値と異なるなら、グレー表示にする、という条件付き書式を入れています。

その他

ここまで作り込めば、あとは全て自動で更新されるので、いつでも最新のカレンダーがスプレッドシート上で確認できます。
カレンダーのそれぞれの日にちの部分は年月日のデータなので、別の列にある日付と合致したら背景色を青くするとかいうことも簡単です。
こういうカレンダーが一つあると、重要な日付をカレンダー上でいつでも見れるようになるのでとても便利です。
自分はこのカレンダー上に、JIRAからリリース日を取得して表示したり、そこから逆算して申請日やQA開始日などを表示したりしています。
皆さんもぜひ使ってみてください。