時刻データ (0:30) を計算できる数値 (0.5) に変換する方法

質問

時刻データ (0:30) を計算できる数値 (0.5) に変換する方法

Google ドキュメントのスプレッドシートにて「時刻データ (0:30) を、計算できる数値 (0.5) に変換する方法」をご教授ください。

エクセルですと、以下のような式を設定することで上記の希望を満たす結果を得ることができるのですが、

「=B2/"1:0:0"」
「=(DAY(A1)*24+HOUR(A1))+(MINUTE(A1)/60) 」

式を設定したエクセルファイルをGoogleドキュメントにアップロードすると正しく表示されません。
（72時間30分＝72.5時間 が 48.5 と表示される）

よろしくお願いします。

回答No.3

SortaNerd · ベストアンサー

なおExcelでもGoogleでもどちらでも正しい値が出るようにするには次の通り。
=IF(ISERROR(DAY("1900/2/29")),(DAY(A1+2)-1),DAY(A1))*24+HOUR(A1)+MINUTE(A1)/60
1900/2/29が存在するかをISERRORで判定して振り分けています。

お礼

ありがとうございます。この方法で対応させていただきたいと思います。

回答No.2

SortaNerd

結論から言うと
=(DAY(A1+2)-1)*24+HOUR(A1)+MINUTE(A1)/60
で出来ると思います。

以下説明ですが面倒なら読み飛ばすことをお勧めします。

これはExcelの変な仕様にGoogleDocsのSpreadsheet(以下Google)が従っていないことが原因です。
Excelは(Googleも)日付と普通の数値を内部では同じように扱い、「書式設定」によって見た目だけを変えることで擬似的に処理しています。
数値「1」は1900年1月1日を意味し、1日ごとに1だけ増えていきます。1日以下の時間は小数で表します。この日付として解釈するときの数値をシリアル値と呼んだりします。
なお、日付と数値の区別がないばかりか、時刻と時間の区別もありません。
なので、30分という「時間」のつもりで入力した0:30は、1900年1月0日午前0時30分という「時刻」として扱われます。
ここで気になるのが「1月0日」という奇妙な日付ですが、仕様です。Excelにとっては1900年には1月0日があるのです。
1日未満の時刻を入れて書式を日付に変えれば見られます。
さらにExcelにはこの1900年を閏年として扱う仕様(バグ?)があります。互換性のためだそうです。
1900/2/29と入れてみましょう。Excelでは日付になり、Googleではエラーが出ます。

しかしGoogleはこの奇妙な仕様には従わない道を選んだようです。
1900年には1月0日も2月29日もありません。
が、それでもなおExcelの呪縛からは逃げられないと見えて、Excelと1900年3月1日以降のシリアル値が同じになるようにしています。
そのためExcelでは「1」=「1900年1月1日」なのがGoogleは「1」=「1989年12月31日」となってしまっています。
これは「1」と入れたセルをYEAR()、MONTH()、DAY()で参照すれば確かめられます。

さて時間を扱う機能が無いとどういう事が起こるか。
HOUR("1:00")で返る「1」という値は、"1:00"の意味する「1900年1月0日午前"1"時0分」の「1」です。
ではDAY("72:30")はというと、「1900年1月"3"日午前0時30分」の「3」が返ります。
決して72時間=3日(間)という意味ではありません。
768(=32*24)時間を入れてDAY()をとれば、1900年2月1日なので「1」が返ってきます。

と、そのような事情がある中シリアル値の始点だけ変えてしまったGoogleは、2/29以前の日付が1日ずれているせいで、Excelで「2日間」を意図して入れられた「2」を「(1900年1月)1日」と扱います。
(DAY(A1+2)-1)の足した+2のうち+1分がこのずれの分です。
さらに、1日未満の時間を扱おうとすると0が(1900年1月)0日でなく(1899年12月)31日を指しているせいで異様に大きな値が出てしまいます。
これを回避するため、+1して、0を1にすることで正常に扱える日付の範囲にした上で、DAYを取って、そこから1を引いて元に戻しています。

お礼

詳細な説明までしていただきありがとうございました。

回答No.1

maron--5

="0:30"*24=0.5

お礼

早速のご回答ありがとうございました。