いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)日時計算の起点は1899/12/30 0:00:00

 Googleスプレッドシートでの日時の扱いについてです。日時計算の起点がExcelとは異なる設定となっています。

  • シリアル値「0」に対応する日時は1899年12月30日0時0分0秒です。Excel(1900年1月0日0時0分0秒)と異なります。
  • マイナスのシリアル値も日時に換算できます。
  • ただしExcelには1900年2月29日が存在しGoogleスプレッドシートには存在しない(この日付は実際は存在しなかったもの)ため、シリアル値61(=1900/3/1)以降はシリアル値と日付の対応が一致します。

手順

 適当なセルに「0:00:00」と入力してみます。
 これは午前0時ジャストを表していますが内部的にはシリアル値、つまりは数値の0として扱われています。よって単に「0」と入力しても、この後の結果は同じものとなります。

 このセルを選んだ状態でメニューから「表示形式」→「数字」→「日時」と選択します。

 

 すると「1899/12/30 0:00:00」と表示されました。
 シリアル値0に対応する日時、つまり日付や時間の計算の起点はこの日時ということになります。
 ただしこれ以前の日付時刻が扱えないわけではなく、たとえば「=TO_DATE(-3)」という式は「1899/12/27」を返します。


 なお、シリアル値と日付の換算に関するExcelとの違いについてですが、Excelには1900年2月29日という日付が存在する(シリアル値60に対応)のに対しスプレッドシートには存在しない(シリアル値60は1900年2月28日に対応)ため、シリアル値61は両者とも1900年3月1日に対応することとなり、それ以降の対応も両者で一致します。もちろん61未満では異なります。

 さて、ここで「1899/12/30 25:00:00」と入力してみます。
 入力を確定すると……

 「1899/12/31 1:00:00」に改められます。もちろん単に「25:00:00」と入力した場合もこの日時とみなされます。

 ここでちょっとした注意が必要です。
 Excelの場合は日時計算の起点が「1900/1/0 0:00:00」なので「25:00:00」と入力すると「1900/1/1 1:00:00」とみなされます。これは(年と月を気にしなければ)「1日と1時間」とみることができるため、日時計算のうえで大変都合がよい設定です。
 しかしGoogleスプレッドシートの場合は「31日と1時間」ということになってしまいます。例えばこの状態でどこかのセルに「=DAY(B2)」と入力すると「31」と返ってきますし、時間換算しようとして「=24*DAY(B2)+HOUR(B2)」と入力すると「745」(=24*31+1)という値が返ってきてしまいます(Excelだと「25」(=24*1+1)が得られます)。