Google スプレッドシートに日付と曜日を関数で入力する

■ やりたいこと

日付を任意の形式で表示する。加えて同一セル内に曜日も記入する。
任意の基準日から開始でき、数式をコピーしてカレンダーの様に扱えるようにする。（ライセンスがないため）未検証だが恐らくGoogle Spread Sheetの他にExcelでも応用できると思われる。

完成イメージ

日付 + 空白スペース + 曜日 を同一セル内に表示させる。

■ 要約

大事なところだけ知りたい方のために要点だけ先に伝える。

1. 1行目に基準日を作る（例として2020/08/12を基準日とする）

=TEXT(44055,"yyyy/mm/dd")&" "&TEXT(44055,"ddd")

曜日が不要な場合は「&」以降を削除する。44055は後述するDATEVALUE関数で取得する。

2. 2行目以降に連続させる数式を入力する

=TEXT(LEFT(A1,LEN(A1)-2)+1,"yyyy/mm/dd")&" "&TEXT(LEFT(A1,LEN(A1)-2)+1,"ddd")

3. 2行目の数式をコピーして量産する

2行目をコピーして3行目以降に貼り付けている。

■ 説明

見た目の割に複雑なので、可能な範囲で細かく説明を行う。

1. 1行目に基準日を作る（例として2020/08/12を基準日とする）

=TEXT(44055,"yyyy/mm/dd")&" "&TEXT(44055,"ddd")

TEXT関数を利用する。TEXT関数はシリアル値を任意日付フォーマットに変換する関数である。

まずはシリアル値を取得する。シリアル値の取得にはDATEVALUE関数を利用する。DATEVALUE関数は日付からシリアル値を取得する関数である。
シリアル値とは1899/12/31を1と数え、日付を整数に変換したものである。値が1増えるごとに1日進むことを意味する。表にすると次のようになり、左にTEXT関数、右にDATEVALUE関数を取る。左右は等価関係である。TEXT関数にシリアル値を渡すと日付になり、DATEVALUE関数に日付を渡すとシリアル値に変換される関係である。2020年8月12日は1899年12月30日から44055日後であるため、「TEXT(44055,"yyyy/mm/dd")」が入力されている。繰り返しになるが、何日後かを調べるためには、上述のDATEVALUE関数を利用し求める。

下記は、セル内の数式

=text(-1,"yyyy/mm/dd") →1899/12/29	    =datevalue("1899/12/29") →-1
=text(0,"yyyy/mm/dd") →1899/12/30	    =datevalue("1899/12/30") →0
=text(1,"yyyy/mm/dd") →1899/12/31	    =datevalue("1899/12/31") →1
=text(2,"yyyy/mm/dd") →1900/01/01	    =datevalue("1900/01/01") →2
=text(3,"yyyy/mm/dd") →1900/01/02	    =datevalue("1900/01/02") →3
...	                        ...
=text(44044,"yyyy/mm/dd") →2020/08/01	=datevalue("2020/08/01") →44044
=text(44045,"yyyy/mm/dd") →2020/08/02	=datevalue("2020/08/02") →44045
=text(44046,"yyyy/mm/dd") →2020/08/03	=datevalue("2020/08/03") →44046

今回は例として2020/08/12を基準日とするため、予めDATEVALUE関数を利用して2020/08/12のシリアル値である「44055」を取得する。

次にTEXT関数に基準値のシリアル値を入力する。もし、TEXT関数とDATEVALUE関数を一度に利用したい場合は、次の式で代用するこもと可能である。人によってはこちらのほうが使いやすいかも知れない。

=TEXT(DATEVALUE("2020/08/12"),"yyyy/mm/dd")&" "&TEXT(DATEVALUE("2020/08/12"),"ddd")

これを先頭行に記入する。

日付フォーマットに「yyyy/mm/dd」を採用しているが、別のフォーマットでもよい。日付フォーマットを変更した場合は2行目以降の「LEN(A1)-2」の「-2」の値を調整する必要があるので注意する。

2. 2行目以降に連続させる数式を入力する

同じ要領でコピー用のセルを作成する。

=TEXT(LEFT(A1,LEN(A1)-2)+1,"yyyy/mm/dd")&" "&TEXT(LEFT(A1,LEN(A1)-2)+1,"ddd")

やりたいことは上の行の値+1を行いたい。TEXT関数は上述したため割愛する。LEFT関数は指定セルの左からN文字を取得する関数である。LEN関数は指定関数の文字数を取得する関数である。「LEFT(A1,LEN(A1)-2)」はA1セルの値を左から10文字取得する、という意味になる。取得した値は「2020/08/12」なので、これをTEXT関数にわたすと日付の整数が取得でき、その整数+1が1日未来になる。

「&」は文字列結合を行う際に利用する。この場合、&が2つある。「&" "」は空白の半角スペース文字を結合している。「2020/08/12 水」の12と水の間の空白である。次の「&」は右側の関数で作成した文字列を左側の文字に連結させている。つまり右側の関数で作成した曜日を半角スペースの後ろに連結させている。

曜日を取得するにはTEXT関数を利用する。TEXT関数の特性として日付フォーマットに「ddd」を入力すると曜日が取得できる。ちなみに「ddd」ではなく「dddd」にすると、「水」→「水曜日」になる。

最終的に左右で作成した文字列を、&を用いてそれぞれ連結させている。

3. 2行目の数式をコピーして量産する

あとは、2行目に書いた数式をコピーして下に貼り付けるだけである。
画像では2020/08/13をctrl + c でコピーし ctrl + v で貼り付けている。

■ まとめ

スプレッドシートで日付を扱いたい場面は少なくない割に、日付を扱う関数は意外とクセがあり、習得が難しい。一度覚えてしまえば、二度目以降は思い出しながら利用できるが、最初の一回目が最難関である。しかし一度覚えてしまえば生産性が上がるためぜひともマスターしたい技である。不明点があればコメントしていただければ解答するかもしれない。