表計算ソフトの日付って、扱いがちょっと独特ゆえ、単純に文字列として入力しているケースも多いです。それも良いけれど、表計算ソフトなら自動で計算させたい。
最近、日付に関する計算をする機会があり、その扱いについて知っていると便利な小ネタをまとめてみました。
「Google スプレッドシート」でも「Microsoft Excel」でも使えます。
8桁の数字を日付に直す方法
日付を「20200401」と、年月日を区切りなく8桁の数字で表すデータ形式はよく使われています。
これをそのまま表計算ソフトにコピーしても文字列として扱われてしまい、計算できません。
そこで、文字列を日付データにする関数がこちらです。
=DATEVALUE(REPLACE(REPLACE(A1, 7, 0, "/"), 5, 0, "/"))
REPLACE関数は開始位置と文字数を指定できるので、文字数「0」を指定すると開始位置に文字を挿入することと同じ結果になります。
先頭から5番目と7番目「/」を入れているのが上記の関数です。
月末を求める方法
「毎月20日」ならDATE関数のに日を「20」に固定すれば簡単です。では、毎月末は？
これは関数というより、発想の転換で解決できます。
=DATE(2020,4+1,1)-1
理屈は簡単で、翌月1日の前日を求めているだけ。
これなら、シンプルに書けて、うるう年にもバッチリ対応します。
協定世界時（UTC）を日本標準時（JST）に直す方法
WordPressはデータベースに、GMT（グリニッジ標準時）が保存されています。
このGMTを調整したのがUTC（協定世界時）です。
ただ、JST（日本標準時）はどちらも「+9時間」ですので、日本人なら気にする必要はないでしょう。
今回、UTCしか出力してくれないデータがあったので、JSTに変換することになりました。
=VALUE(A1)+TIME(9,0,0)
日付を1日減らすには整数「1」を引けば良いですが、時間は小数点以下の計算が必要なので専用の関数を使う方が良いでしょう。
そもそも、表計算ソフトの日付データってどんな仕組み？
「シリアル値」と呼ばれる仕組みです。
これは「1900年1月1日」を「1」とし、そこから経過した日数で管理しています。※Excelのバージョンによって「1904年1月1日」がシリアル値「1」になっている場合があります。
例えば、
- 「1900年1月1日」は、シリアル値「1」
- 「1900年1月2日」は、シリアル値「2」
- 「1900年1月3日」は、シリアル値「3」
ですので、1日前を求めるなら、整数の「1」を引けば良い、となるわけです。
ちなみに、「2020年4月1日」は「43922」でした。
時刻は小数点以下で計算されていて、1時間は「1 ÷ 24」、1分は「1 ÷ 14400」、1秒は「1 ÷ 86400」です。
難しい計算は表計算ソフトにお任せします（笑）
まとめ
日付データのシリアル値について理解できると、できることが格段に増えます。例えば、今日から30日後は？という計算も簡単に。
久しぶりに触れてみて「あれっ？どうだったっけ？」ってなってしまったので、忘れないようにまとめてみました。
普段、日付を手入力している方のお役に立てたら嬉しいです。
