Googleスプレッドシートで時間計算する時の罠
Googleスプレッドシートで労働時間や稼働時間のデータの集計を行う場合に、多くの人が最初に嵌まるポイントがあります。それが「計算結果がオカシイ」「オカシイことに気がつけずに間違った資料を作成する」という点。これ使い慣れていても沼るポイントで、あとで計算が大幅に変わってしまったり、労働時間計算の場合、給与計算という人のお金に直接影響したりするので、謝罪では済まないことがあります。
そこで、スプレッドシートで「時間計算をする時に沼るポイント」と解法をまとめました。
目次
今回利用するファイル等
- 時間計算の罠 - Google Spreadsheet
コメントを入れています。各セルには個別に書式設定や、関数が入っています。以下のような分類になっています。
- 赤字は書式設定ミスや関数の計算結果が間違えている事例です
- 青字は書式設定や関数の計算が正しい事例です
- 緑字は24H以下だから正しく計算出来てるだけで、24Hを超えるとアウトになります。
ポイントと解決法
沼るポイント
主に沼るポイントとしては以下の２点が挙げられます
- 09:00:00といった時間形式の値を集計した結果がオカシナ表示
- 2024/11/21 9:00:00といった日時形式の値で開始時間と終了時間の差し引きをした結果がオカシナ表示
気がつけばよいのですが、全部の行の結果を検算せずに進めてしまい、下手に正しい値を算出している行を見つけてOKとしたら、実はほかが間違っていたというケースが非常に多く見受けられます。その多くが「書式設定が間違っていたり、Hour関数・Minutes関数を使ったが為に24H以上がリセットされて計算されてることを知らない」事が原因です。
故に、25:00:00を関数で処理をすると1時間分しかでてきていなかったとか、その集計をした結果が24時間以下の値としてまとめられていたなどが代表的な事例です。また、給与計算などでは分換算して1分単位で残業時間計測を出したりするのですが、これが大幅に間違っていて、支給金額をミスって怒られるということに直結します。
図：計算ミスをしてるケース
なぜこのような問題が起きるのか？
表示形式の問題
Googleスプレッドシートの書式設定の１つに「表示形式」がありますが、9:00:00といった値の表示に対して「時間」という書式を割り当てがちです。これが大きな問題です。
この時間という表示形式は24Hを超える値（例：27:30:00）といった場合には、2:30:00といった具合に24Hを超えてリセットした後の値が表示されてしまいます。当然この値を元に分換算を実行しても、2時間30分を分に換算した値として出されてしまう為、特にsumifなどの集計では要注意の項目です。
デフォルトの「自動」であってもNGですのでまずは集計してる列（またsumifの集計対象列も含まれる）の書式設定が「時間」になっていないか？確認しましょう。
※この経過時間指定をしていないと、スプシの右下の合計表示も24Hリセットされて合計されてしまうので要注意。
数式の問題
また、各種ブログ等で時間計算に於いて、9:00:00などの値から時や分を抽出する関数として、Hour関数やMinutes関数を紹介してるものが見受けられます。しかし、きちんとその副作用や注意点を掲示してるものが少なく、これも24Hを超えてリセットした後の値が抽出されるということを明示していないものが多いです。
よって、27:30:00からHour関数で時を取り出すと2という値が返ってきてしまいます。よって計算してる列に於いて24時間を超える時を算出する場合に、Hour関数・Minutes関数は使ってはなりません。
ちなみに、「2024/11/21 18:00:00」といった形式の値同士で差し引きは単純に=B3-B2で計算して時間の差分を計算出来ますが、こちらも上記の表示形式を経過時間にしないと24Hを超えたものがリセットされて計算されてしまうので要注意。
鉄守べき解決法
Excel時代から言われてることなのですが、労働時間の合計は24時間を超えたりしますし、分に換算して計算する必要があります（労働基準法上、1分単位で支給が必要です）。その時にいつものような感覚で計算すると大間違いを犯す可能性があります。
故に時間計算をするシートでは以下の2点を鉄守しましょう。
- 時刻列はすべて書式は経過時間で指定する
- 集計した値から時や分を取得する場合には、=VALUE(TEXT(B4,"[M]")の数式を使い、Hour関数やMinutes関数は利用しない。
9:00:00といった値から時間を抽出する場合には、=VALUE(TEXT(B2,"[h]"))を利用すれば、時だけがきちんと抽出されます。上記２点を必ず守って計算するようにしましょう。
図：書式は経過時間にしましょう
図：時間計算にHourやMinutesは使わない
カスタム関数を利用する
表示形式やHoursといった関数に振り回されるのが嫌だなぁという時には、カスタム関数を使うと忠実に計算させる事が可能になります。そのための関数を自分でGASで構築します。ここでは2種類の関数を作ります。例えば90:00:00 = 5400分という具合に分に変換する式や、2024/11/21 9:00:00と2024/11/23 18:00の差分から分を算出するといった関数になります。
hh:mm:ss形式を分に変換
この場合、カスタム関数にそのまま値を渡すと見た目の値のまま渡るわけじゃなく、またtoLocaleDateStringで変換をかけてしまうと思った値にならないので、Googleスプレッドシートの関数の力を借りつつ、変換をかけて上げます。
カスタム関数としては以下のようなものを書いてあげます。
|
1 2 3 4 5 6 7 8 9 10 11 |
//時刻形式の値を分に換算する function convertMinutes(time) { //各単位を分割して配列に格納する const [hours, minutes, seconds] = time.split(':'); //分に変換 let tempmin = Number(hours) * 60 + Number(minutes); //結果を返す return tempmin } |
実際に利用する場合には、90:00:00という値をTO_TEXT関数で見た目のままの値として渡してあげるので、
|
1 |
=convertMinutes(to_text(A2)) |
といった形にすると、スッキリ分で変換された値が算出されます。
図：合せ技でスッキリ素直に変換
日時の差分を分で計算する
日時の差は「=B2-A2」みたいな単純な引き算で計算出来なくもないですが、書式設定に影響を受けてしまうのでこれもカスタム関数で直接的に差分を分で計算するようにします。
例えば2024/11/21 9:00:00と2024/11/23 18:00:00の差分は、3420分（57時間）になるのですがカスタム関数としては以下のような関数を作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
//２つの日付の差分を分で計算する function diffdatetime(time1,time2){ //２つの日付を取得する let starttime = new Date(time1); let endtime = new Date(time2); //日付の差分を計算する let diff = endtime.getTime() - starttime.getTime(); //分に変換する let difftime = diff / (60*1000); //結果を返す return difftime; } |
実際に利用する場合、starttimeとendtimeの差分となるので、開始日時, 終了日時といった形でそれぞれ値を引数に指定します。
図：スッキリ分で差分を算出出来ました
