Excelを使って勤務時間などを計算する際にミスを防ぐための基礎知識：Tech TIPS

Excelでアルバイトの勤務表を作成する、というのはよくあることだ。ただ、時間は60進法（秒と分）と12進法（時間）が混ざっているので、気を付けないと誤った計算を行ってしまう。そこで、ここでは勤務表を例に、Excelでよく使う時間の計算を解説する。

[小林章彦，デジタルアドバンテージ]

対象：Microsoft Excel 2010／2013／2016

　昨今、「働き方改革」の名の下、労働時間の厳格な管理が求められるようになっている。そのため、社員やアルバイトの出勤時間や退社時間を記録し、Excelで集計したいという要望も増えているようだ。ただ時間の計算は、60進法（秒と分）と12進法（時間）が混ざっているため簡単ではない。

　そこで本TIPSでは、勤務表を例にして、Excelで時間の計算をするためのポイントをまとめて紹介する。

Excelの時刻の表し方

　Excelの時刻は、0〜0.99988426の範囲内の値（「シリアル値」という）で、午前0時（0:00:0）から午後11時59分59秒（23:59:59）までを表している（24時間を1.0と見なした小数）。昼の0時（正午、12:00:00）は、「0.5」ということになる。

　セルの表示形式で［時刻］に設定したセルから、［標準］に設定されたセルに値をコピーすると、このシリアル値が表示されることがあるのは、内部ではこのような小数点の数値で時刻を表しているからだ。

　この仕組みが分かると、Excelでの時間計算でミスが少なくなるはずだ。

基本的な勤務時間の計算

　下画面で示した勤務表を例にして、基本的なタイムシートの設定と勤務時間の計算方法を解説していこう。

勤務表の例
こうした勤務表はよく使われているだろう。この勤務表を例にExcelの時間計算について説明していく。

　まず、時刻を入力するセルの書式設定を変更しておこう。

　該当するセルを選択し、右クリックメニューから［セルの書式設定］を選択する。［セルの書式設定］ダイアログの［表示形式］タブを開き、「分類」で［時刻］を選択し、種類で「13:30」を選ぶ（秒までの表示が必要であれば、「*13:30:55」を選ぶ）。これで、選択したセルの表示が時刻表示となる。

セルの書式設定を行う（1）
時刻を入力するセルを選択し、［セルの書式設定］で表示形式を［時刻］にしておこう。そうしないと、シリアル値が表示されてしまうことがある。

▼

セルの書式設定を行う（2）
［セルの書式設定］ダイアログが表示されたら、［表示形式］タブを選択し、「分類」で「時刻」を選び、「種類」（表示形式）を「13:30」とする。

　次に始業時刻には仕事を開始した時刻、終業時刻には仕事が終わった時刻を入力する。休憩時間はお昼休みや休憩などの合計時間を入力するものとする（もちろん、お昼休みや休憩時間を分けて入力するようにしてもよい）。

　勤務時間は、「=＜終業時刻セル＞-＜始業時刻セル＞-＜休憩時間セル＞」で求められるので、これに該当する式を勤務時間の列に入力していけばよい。例えば、「05/01」の行であれば、勤務時間のセルには、「=D7-C7-E7」という式が入る。

勤務時間を計算する
勤務時間は、「=＜終業時刻セル＞-＜始業時刻セル＞-＜休憩時間セル＞」で求められるので、「05/01」の行ならば、「=D7-C7-E7」という式を「勤務時間」列に入力する。

　この式を勤務時間列のセルにコピーしていけば、その日の勤務時間が計算できる。

休憩時間が一定の場合はTIME関数を利用する

　休憩時間が常に決まっているような場合は、その決まった休憩時間をシリアル値に変換して「=＜終業時刻セル＞-＜始業時刻セル＞」から引く必要がある。

　そのためには、「TIME関数」を利用する。TIME関数は、「TIME(時, 分, 秒)」という書式になっており、時、分、秒の引数を与えることで、シリアル値を返すものだ。例えば、12時30分のシリアル値は「TIME(12,30,0)」で計算でき、「0.520833」の値を得ることができる。

　休憩時間が常に1時間30分と決まっているのであれば、「=＜終業時刻セル＞-＜始業時刻セル＞」から「TIME(1,30,0)」を引けばよい。なお、TIME関数を入力したセルの表示形式が［標準］に設定されている場合は、日付形式で表示されるので注意が必要だ。シリアル値で表示したい場合は、セルの表示形式を［数値］に設定しておく必要がある。

勤務時間の合計を正しく表示する

　勤務表では当然ながら1カ月の合計勤務時間の計算が必要になるだろう。しかし単純に勤務時間の合計を求めるために、「SUM（＜勤務時間の列＞）」として「勤務時間」列の値を合計すると、合計時間が24時間を超えて日付が繰り上がり、正しく表示されない（本来なら「130:53」などとなるところ、「10:53」といったように表示される。これは5日と10時間53分から、日の部分を省略した状態）。

　このように24時間を超えた時間を表示させたい場合は、セルを選択してから［セルの書式設定］ダイアログの［表示形式］タブを開き、「分類」で［ユーザー定義］を選択、「種類」の入力ボックスに「[h]:mm」と入力すればよい。これで、そのセルに24時間を超えた時間が表示される（[h]は24時間を超えた時間をそのまま表示させるための指定。同様に[m]や[s]を使うと、60分や60秒を超える値をそのまま表示できる）。

勤務時間の合計を正しく表示する（1）
セルの表示形式に「13:30」を選択した場合（これは「h:mm」とほぼ同じ）、24時間を超えると日付が繰り上がり、0時に戻ってしまう。そのため、24時間よりも大きな値が表示されない。

▼

勤務時間の合計を正しく表示する（2）
24時間を超えても正しい時間が表示されるようにするには、セルの表示形式を「[h]:mm」にする。

勤務時間が日付をまたぐ場合

　夜勤やシフト勤務などがある場合、始業時刻の値が終業時刻の値よりも大きくなってしまい、単純に「=＜終業時刻セル＞-＜始業時刻セル＞」では計算できない。

　このような場合、IF関数を使って、「=IF(＜始業時刻セル＞ > ＜終業時刻セル＞,1 - ＜始業時刻セル＞ + ＜終業時刻セル＞,＜終業時刻セル＞-＜始業時刻セル＞) 」とすればよい。＜始業時刻セル＞が＜終業時刻セル＞よりも大きければ、「1-＜始業時刻セル＞」で24時から＜始業時刻セル＞を引き、そこに＜終業時刻セル＞を足せば、日付をまたいだ勤務時間が計算できる。＜始業時刻セル＞が＜終業時刻セル＞よりも大きくなければ、通常の勤務時間の計算と同様、「=＜終業時刻セル＞ - ＜始業時刻セル＞」で計算すればよい。

勤務時間が日付（深夜0時）をまたがる場合の処理
夜勤やシフト勤務などで、終業時刻が翌日になってしまうような日が混在しているような場合、勤務時間の計算処理に工夫が必要となる。IF文を使って、そのような日は、「1-＜始業時刻セル＞」で24時から＜始業時刻セル＞を引き、そこに＜終業時刻セル＞を足すようにすればよい。

給与を計算する

　勤務時間が計算できたら、時給を掛けて、給与を計算したいところだろう。ただ、単純に計算した合計勤務時間に時給を掛けても、正しい給与は得られない。

　前述の通り、Excelの時刻表示は、24時間を0〜0.99988426間の値で示したものである。12時のシリアル値は「0.5」なので、合計勤務時間が12時間で、時給が1000円だった場合、単純に掛けてしまうと、給与は500円というおかしな値になってしまう。

　実は、シリアル値を24倍すれば、時間の値となる（分と秒は時間の小数点で表される）。つまり、「時給×合計勤務時間×24」がその月の給与だ。

給与を計算する
時間のシリアル値は前述の通り、実数の24の1なので、時間を計算するには勤務時間を24倍にすればよい。

「Tech TIPS」