Googleスプレッドシートで勤怠管理をしていて、「24時間以上の時間を合計したら、計算結果がおかしくなった！」という経験ありませんか？スプレッドシートで時間の計算をしていると、計算結果が意図した値にならないことがあります。
今回は、Googleスプレッドシートに記載された時間の計算方法について、スプレッドシート上で完結する方法と、Google Apps Script(GAS)を使う方法について、それぞれ実例を交えて説明します。
Googleスプレッドシートで24時間以上の時間がおかしい
Googleスプレッドシート上で、以下の勤務表の計算をするとします。
求めたいのは「勤務時間合計」と「給与」です。
| 項目 | 概要 | 関数 |
|---|---|---|
| 勤務時間合計 | D列の勤務時間の合計 | =SUM(D2:D11) |
| 給与 | 時給 × 勤務時間合計 | =F2*G2 |
しかし、実際の計算結果が明らかにおかしいです。
スプレッドシートでの時間の扱い
スプレッドシート上に「10:00」と入力したセルの値を、GASで出力してみると「Sat Dec 30 1899 10:00:00 GMT+0900」と出力されました。
1899年12月30日の10:00となっています。
スプレッドシートの時間はDate型
スプレッドシートの時間は「Date型」で扱われます。Date型は、「1899年12月30日 0:00」が起点となっており、「10:00」のように時刻だけセルに書き込んだ場合は内部で補完され「1899年12月30日 10:00」になります。
「勤務時間合計」のセルについて、GASで中身を見てみます。
「Mon Jan 01 1900 10:00:00」となっています。
「1900年1月1日 10:00」は、Date型の起点である「1899年12月30日 0:00」から58時間経過しています。
つまり、スプレッドシート上で10:00と表示されていた時間は、内部ではDate型の1900年1月1日 10:00となっていたということです。
24時間以上の時間を計算する方法
24時間以上の時間を計算する方法について、スプレッドシート上で設定する方法と、GASで計算する方法の2種類を紹介します。
スプレッドシート上で設定する方法は、セルの設定に依存するため、書式設定がクリアされたり他シートにコピーした場合に計算結果がおかしくなる可能性があります。GASで計算する方法では計算結果を文字列として扱うため、環境による値のズレは生じませんが、その値をもとに計算する場合型変換が必要です。状況に応じて適した方法を選んでいただければと思います。
【その１】スプレッドシートのセル設定を変更する方法
Googleスプレッドシートのセル設定を変更する方法を紹介します。この方法が一番楽に対応できます。
設定したいセルを選んでいる状態で、表示形式の詳細設定（「123」と書いてあるボタン）を押します。
「経過時間」を選びます。
勤務時間合計が正しく表示されるようになりました。
計算する際は時間に24をかける
勤務時間合計は正しく計算されましたが、給与がおかしな値のままです。表示形式をかえただけなので、内部では「1900年1月1日 10:00」として扱われているためです。
// 時給 × 勤務時間合計 で給与を算出
= F2 * G2 * 24
これで、勤務時間合計と給与が正しく算出されました。
【その２】：GASで24時間以上の時間計算をする方法
GASで24時間以上の時間を足し算する方法は、
- 「hh：mm」をhh(時間)、mm(分)に分ける
- 時間、分をそれぞれ足し算する
- 分の合計を60で割り、整数部分は時間に足す
- 最後に時間、分を文字列連結させ「hh：mm」の形にする
となります。例えば、10:45 + 5:30 の場合、時間・分それぞれを足し算します。
すると、分が75になりました。75分を60(=1時間)で割ると、1.25になります。整数部分の1はそのまま時間に足し、0.25は60を掛けて分に直します。それぞれを文字列連結させると、「16:15」となります。
上記をGASのスクリプトにすると以下になります。
const sheet = spreadSheet.getSheetByName("勤務表");
function CalcTime() {
let hSum = 0; // 「時間」格納用
let mSum = 0; // 「分」格納用
let lastRow = sheet.getLastRow(); // 勤務表の最終行
for (i = 2; i <= lastRow; i++) {
// 「勤務時間」列の値を文字列で取得
let strTime = sheet.getRange(i, 4).getValue().toString();
// 最初の「:」の位置を取得
let index = strTime.indexOf(":");
// 時間・分をそれぞれ抽出
let hour = strTime.substring(index - 3, index);
let min = strTime.substring(index + 1, index + 3);
// 時間・分を数値型に変換
hour = Number(hour)
min = Number(min)
// 時間・分の合計
hSum = hSum + hour
mSum = mSum + min
// 「分」の小数部のみ取り出し
dcm = mSum / 60 // 例) 90分→1.5時間
let dcmMin = (dcm - Math.trunc(dcm)) * 60; // 整数部分は「時間」に加算する
let resultMin = Math.round(dcmMin).toString().padStart(2, '0'); // 四捨五入した結果を「分」に格納
// 時間の合計に分の整数部を足す
let resultHour = hSum + Math.trunc(dcm);
// 時間・分の合計値を文字列で結合する
result = resultHour + ":" + resultMin;
// スプレッドシートに書き込み
sheet.getRange("G2").setValue(result);
}
}実行結果は以下になります。
計算結果は文字列として扱われますので、スプレッドシート以外（Googleドキュメントや、LINEやSlack等の外部アプリ）に結果を渡す際も時刻のズレが生じません。逆に言うと、ここからさらに計算をする場合には日付型に変換する必要があります。
【その３】GASでシリアル値で計算する方法
次に、Google Apps Script(GAS)で計算する方法を紹介します。
GASで時間をシリアル値に変換するにはtimeSerialメソッドを使います。
var val = new Date("2023/03/11");
var timeSerial = val.getTime();
console.log(timeSerial); // 出力結果は1678460400000シリアル値からDate型にするには、date = new Dateの引数にシリアル値を入れます。
var date = new Date(timeSerial);
// 出力結果はSat Mar 11 2023 00:00:00 GMT+0900 (Japan Standard Time)
console.log(date);シリアル値の計算における注意点
シリアル値⇔日付型の変換ができるのであれば、シリアル値で足し算した結果を最後に日付型に変換してスプレッドシートに書き込めば良いんじゃないか？と思うところですが、実際にやってみるとうまくいきません。
「2023/03/12 0:00」となるのかと思いきや、2076/03/18 15:00となってしまいました。そこで、date = new Dateの引数に0を渡して、基準日時を調べてみました。
すると、「Thu Jan 01 1970 09:00:00 GMT+0900 (Japan Standard Time)」となっていました。
JavaScriptでは1970年1月1日が基準となっているようなのですが、GASで実行するとデフォルトでは日本時間(GMT-9時間)となっています。また、先ほどのスプレッドシートの説明では「1899年12月30日 0:00」が起点となっていると説明しましたが、GASでは「1970年1月1日 0:00」が起点になっています。もう訳が分かりませんね・・・これらをまとめると以下のようになります。
- スプレッドシートとGASのDate型の基準時間は異なる
- GASのDate型は実行環境(地域)によって時刻が異なる
参考：https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Date
ネットで調べてみると、シリアル値で計算するには、UNIX時間の経過日数と変換したい時間のシリアル値の差分をミリ秒換算して日付変換をするという方法になるようです。詳しくは以下記事をご参照ください。
参考：https://dev.classmethod.jp/articles/seriall_changedate/
まとめ
今回は、Googleスプレッドシートに記載された時間を計算する方法について紹介しました。24時間以上の計算をすると、デフォルトではおかしな値になっていますが、スプレッドシート上で対応が可能であること、GASのスクリプトからも対応できることが分かりました。目的に応じて、適した方法を選ぶと良いかと思います。
なお、Excel VBAで24時間以上の時間計算をする方法については過去記事にて紹介しています。
コメント