Googleスプレッドシートでは24時間以上の時間を表示することができますが、値をHOUR関数で評価すると24未満の値しか取得できません。
そこで、別の方法で24時間以上の時間部分を取得する方法を示します。
そこで、別の方法で24時間以上の時間部分を取得する方法を示します。
- 「=VALUE(TEXT(時間,"[h]"))」という式により24時間以上の時間の時間部分を取得できます。
手順
まず24時間以上の時間表示ですが、メニューから「表示形式」→「数字」→「経過時間」と選択することで、24時間以上の時間を表示させることができます。
画像ではC5セルにこの設定を適用していますが、詳しくは次の記事をご覧ください。
さて、ここで適当なセルに「=HOUR(C5)」と入力してみます。一見「29」という値が得られそうですが……
結果は「5」となります。
これは、24時間が1日に繰り上げられてしまうためです。
表示形式の変更に伴いHOUR関数の結果が変わるのも変なので、これはやむを得ないといえるでしょう。
そこで式を次のように変えてみます。
=VALUE(TEXT(C5,"[h]"))
TEXT関数を用います。第2引数として24時間以上の表示に対応する「[h]」を指定するのがポイントです。
これで今度は「29」という値が得られました。
なお、24時間が1日に繰り上がっていることを考慮して「=24*DAY(C5)+HOUR(C5)」としてもよさそうです(Excelではうまくいきます)が、そうすると「749」という結果になってしまいます。
これはGoogleスプレッドシートの日時計算の起点が1899年12月30日であることから、DAY(C5)の結果が「1」ではなく「31」になるためです(24*31+5=749)。