日付や時間を数値として計算する方法

給与計算や時間あたりのコスト計算などで、日付や時間などの型のものを数値として計算したいという場合の対処法です。

日付計算の時のよくあるミス。

HOUR()やMINUTE()といった関数を使って計算するというパターン。これ自体は問題ないと言えばないのですが、24時間を超える時間を計算する時に困ったことになります。

例えば、32時間30分00秒という時間を計算する場合。HOUR関数では以下のような結果になります。

spreadsheet1

そうなんです。32時間を24時間+8時間と計算し、8時間だけを返してくるのです。良さそうな関数を探したのですが、見つかりませんでした。

日付型を数値に変換して扱う

対処法は以外とシンプルで、日付型を数値に変換することで計算ができます。

使うのは、VALUE()関数。公式マニュアルには以下のように説明があります。

VALUE()
Google スプレッドシートで認識される日付、時刻、番号の書式の文字列を数値に変換します。

具体的には、以下のようにVALUE()関数に日付をキャストして数値に変換し、24時間表示にするために×24します。 spreadsheet3

分表示にするには、×24×60すればOKです。 spreadsheet4

補足:VALUE()関数とTO_PURE_NUMBER()関数

ちなみに、似たような関数でTO_PURE_NUMBER()という関数があり、ほとんどVALUE()と同じような説明がマニュアルには記載されています。

TO_PURE_NUMBER()
日付/時刻、百分率、通貨などの表示形式を持つ数値を表示形式なしの純粋な数値に変換します。

今回の日付型を計算する場合は、どちらでも使えると言えば使えます。

ただし、引数に文字列を渡した時の挙動が違うようです。引数に文字列「Time」を渡した場合の具体例は、以下のようになります。

spreadsheet2

VALUE()ではエラーに、TO_PURE_NUMBER()ではなぜか引数そのままの「Time」が表示されることに。

ということで、普通に使う場合はエラー表示もしてくれて、タイプ数も少なくなるVALUE()関数を使えば良いのかなと思います。