今回は、数式や関数を使って「時間」を計算する例として、アルバイトの勤務時間と給与をExcelで自動算出する方法を紹介していこう。Excelは「時間」も数値の一種として扱うため、数式や関数を使って普通に「時間」を計算することも可能である。ただし、「時間」ならではの注意点も少なからず存在する。具体的な例を見ながら確認していこう。

入時刻と退時刻から勤務時間を計算

勤務時間を求める場合など、「時間」の計算を行いたい場合もあるだろう。時間は60進法（分、秒）や24進法（時）で表現される数値となるが、このような場合でも数式を使ってさまざまな計算を行ったり、関数SUMで「合計」を求めたりすることが可能だ。

そこで今回は、Excelで時間を計算する方法と注意点を紹介していこう。特に「24時間以上」の時間を表示するには少し特殊な書式指定が必要になる。この機会に覚えておくとよいだろう。

  • 関数SUMで「時間」の合計を計算、24時間以上を表示するには？

今回は、アルバイトの勤務時間と給与を計算する場合を例に、具体的な手順を紹介していこう。以下の図は、あるアルバイトの「入時刻」と「退時刻」を一覧表にまとめたものだ。こういった表は1ヵ月単位で作成するのが一般的かもしれないが、全体を見渡しやすいように、ここでは1週間（7日分）のデータだけを取り出して紹介していこう。

  • 入時刻と退時刻をまとめた表（タイムカード）

まずは、各日付における勤務時間をExcelで計算する。この職場ではアルバイトがシフト制になっているため、毎日の「入時刻」と「退時刻」は一定ではない。このため、「勤務時間」も日によって大きく異なっている。

このような場合は、いちいち手計算で「勤務時間」を算出するのではなく、Excelに計算を任せてしまった方が効率的だ。「勤務時間」は（退時刻）－（入時刻）で計算できるので、これをそのまま数式で入力する。

  • 勤務時間を計算する数式

「Enter」キーを押すと、「退時刻」と「入時刻」の時間差が計算結果として表示される。これで「勤務時間」を算出できたことになる。あとは、この数式をオートフィルでコピーするだけだ。

  • 数式をオートフィルでコピー

これで各日の「勤務時間」を即座に算出できる。このようにデータが「時間」の場合であっても、一般的な数値と同じように数式でさまざまなを計算を行うことが可能である。

  • 自動計算された勤務時間

ただし、「退時刻」が深夜24:00を超える場合は少し注意が必要になる。深夜営業している店舗の場合、勤務終了が深夜の1時や2時になるケースもあるだろう。たとえば、「退時刻」が深夜の1時30分であった場合、単純に「1:30」と入力すると、数式の計算結果はエラーになってしまう。

  • 計算結果がエラーになる例

このような場合は、翌日の1:30を「当日の時刻」として考えて「25:30」と時刻を入力しなければならない。

  • 翌日の1:30を25:30と入力

「Enter」キーを押すと、時刻の表示が「1:30」に自動変更される。と同時に、正しい「勤務時間」を算出することが可能となる。

  • 勤務時間が正しく計算された例

このように深夜24:00をまたいで勤務が続く場合は、時刻の入力に少しだけ工夫が必要となる。間違えないように注意しておこう。

関数SUMを使った「時間の合計」と「24時間以上の表示」

続いては、「関数SUM」を使って1週間の「勤務時間の合計」を求めてみよう。この場合も、関数の入力に特に変わった点はない。普通に「=SUM(合計するセル範囲)」と関数を記述すればよい。

  • 関数SUMの入力

「Enter」キーを押すと、その計算結果が表示される。今回の例では「勤務時間の合計」は20時間30分となった。

  • 関数SUMの計算結果

このように関数SUMを使って「時間の合計」を算出することも可能である。ただし、この場合も注意点が存在する。それは「時間の合計」が24時間以上になる場合だ。

たとえば、先ほどの表に以下の時刻データを追加したとしよう（セルの背景が黄色い部分）。すると、「時間の合計」もプラス6時間されるはずであるが、その表示は「2:30」という少しおかしな表記になってしまう。

  • 合計が24時間を超える場合の時間表示

このような結果が表示されるのは、合計の計算そのものが間違っている訳ではなく、「時間の表示方法」に問題があるためだ。「2:30」の表示は、本当は「1日と2時間30分」を示している。つまり、「26時間30分」という訳で「時間の合計」そのものは正しく計算されている。

Excelでは、特に書式を指定しないで「時間の計算」を行った場合、時間はh:mmという形式で表示される仕様になっている。これは、0～23（時）と00～60（分）で時間（時刻）を表示し、「日」の部分は表示しない、という表示形式になる。

先ほどの例の場合、本当の計算結果は「1日と2時間30分」であるが、「1日」の表示を省略して「2:30」と表示されている、という挙動になる。

このように「時間の合計」が24時間以上になる場合は、合計を適切に表示するように「セルの表示形式」を自分で指定しなおす必要がある。具体的には、以下のように操作すればよい。

1.「時間の合計」を算出するセルを選択し、「セルの書式設定」を呼び出す。

  • 「セルの書式設定」の呼び出し

2.「表示形式」タブで「ユーザー定義」を選択し、種類に[hh]:mm（または[h]:mm）と入力する。

  • ユーザー定義の表示形式に[hh]:mmを指定

「OK」ボタンをクリックして「セルの書式設定」を閉じると、「時間の合計」が適切に表示されているのを確認できる。

  • 24時間以上を正しく表示できた例

このように24時間以上の時間を表示するには、「セルの表示形式」に「ユーザー定義」を指定し、「書式記号」を使って表示形式を指定する必要がある。

念のため、[hh]:mmの書式記号について補足しておこう。hhは「時」を00～23の2桁で表示する、mmは「分」を00～59の2桁で表示する、ということを示している。これだけを指定した場合、時間は00:00～23:59という形式で表示される。つまり、時刻の表示に適した形式になる。

一方、「時間の合計」のように24時間以上も表示したい場合は、hhの前後を[ ]で囲む必要がある。すると、「時」の表示が00～23ではなく、24以上も表示できるようになる。

時間の計算を行うときは、このような注意点があることを認識しておかなければならない。特に「24時間以上の表示」は初心者がつまづきやすいポイントなので、この機会に必ず覚えておこう。

「勤務時間の合計」から給与（バイト代）を計算する

最後に、勤務時間の合計から給与（バイト代）を計算する方法を紹介していこう。「残業」や「深夜勤務」（賃金の割増）がないものとして考えると、給与は（時給）×（勤務時間の合計）で計算できる。ということで、以下のように数式を入力すればよい、と思う方もいるだろう。

  • （時給）×（勤務時間の合計）を計算する数式

しかし、これは正しくない計算方法といえる。先ほど示した数式の計算結果は以下のようになり、1,215円という誰が見ても不可解な数値が表示されてしまう。

  • 数式の計算結果（失敗例）

このようなミスは、時間の「26:30」は数値の「26.5」になるはず・・・、という考え方に起因している。一般的な考え方からすると、「26時間30分を数値で示すと26.5になる」というのは正しい考え方だ。しかし、Excelはそう考えてくれない。

では、どうすればよいのか？　その答えは「さらに24を掛け算する」となる。つまり、（時給）×（勤務時間の合計）×24で給与を計算することになる。数式で示すと、以下の図のようになる。

  • 給与を正しく計算できる数式

この計算結果は29,150円になり、（1,100円）×（26.5時間）と同じ結果になる。つまり、給与を正しく計算できたことになる。

  • 数式の計算結果（成功例）

こちらも初心者の方が勘違いしやすいポイントなので、間違えないように注意しておこう。なお、「なぜ24を掛け算する必要があるのか？」を理解するには、シリアル値について学んでおく必要がある。Excelで「日付」や「時刻」を計算するときに必須の知識となるので、これについては次回の連載で詳しく解説していこう。