(Gスプレッドシート)24時間以上の「時」(HOUR)を取得する

 Googleスプレッドシートでは24時間以上の時間を表示することができますが、値をHOUR関数で評価すると24未満の値しか取得できません。
 そこで、別の方法で24時間以上の時間部分を取得する方法を示します。

  • 「=VALUE(TEXT(時間,"[h]"))」という式により24時間以上の時間の時間部分を取得できます。

手順

f:id:accs2014:20200618033850p:plain:right:w500

 まず24時間以上の時間表示ですが、メニューから「表示形式」→「数字」→「経過時間」と選択することで、24時間以上の時間を表示させることができます。
 画像ではC5セルにこの設定を適用していますが、詳しくは次の記事をご覧ください。

www.officeisyours.com
 

f:id:accs2014:20200618033854p:plain:right:w500

 さて、ここで適当なセルに「=HOUR(C5)」と入力してみます。一見「29」という値が得られそうですが……

f:id:accs2014:20200618033857p:plain:right:w500

 結果は「5」となります。
 これは、24時間が1日に繰り上げられてしまうためです。
 表示形式の変更に伴いHOUR関数の結果が変わるのも変なので、これはやむを得ないといえるでしょう。

 

f:id:accs2014:20200618033903p:plain:right:w500

 そこで式を次のように変えてみます。

=VALUE(TEXT(C5,"[h]"))

 TEXT関数を用います。第2引数として24時間以上の表示に対応する「[h]」を指定するのがポイントです。
 

f:id:accs2014:20200618033906p:plain:right:w500

 これで今度は「29」という値が得られました。

 

f:id:accs2014:20200618033910p:plain:right:w400

 なお、24時間が1日に繰り上がっていることを考慮して「=24*DAY(C5)+HOUR(C5)」としてもよさそうです(Excelではうまくいきます)が、そうすると「749」という結果になってしまいます。
 これはGoogleスプレッドシートの日時計算の起点が1899年12月30日であることから、DAY(C5)の結果が「1」ではなく「31」になるためです(24*31+5=749)。