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は使わない
