エクセル勤怠管理で労働時間計算が微妙に狂う理由と簡単な対処方法

エクセルで勤怠管理をするときに必ず労働時間計算を行いますが、このとき注意しなければならない問題があります。それは「時間計算の計算過程で誤差がでることがある」というものです。

誤差が出ると給与計算や労働基準法遵守に影響するため、労務担当者にとっては頭の痛い問題です。しかし時間計算機能のある電卓で計算すると誤差がでないため、中には電卓で計算した労働時間を、エクセルに手入力する方もいます。

電卓で時間計算する場合、従業員が数十人以上いると、1ヶ月分の労働時間計算だけ数日かかります。しかし電卓計算と手入力は、かえって計算ミスしやすく、また効率がよくありません。なんとかエクセルで一気に計算したいものです。

このような時間計算の誤差を解決する方法はいくつかありますが「TEXT関数」を使う方法がオススメです。電卓で計算する必要も、エクセルに手入力する必要もなくなり、正確で効率的に労働時間計算、給与計算ができるようになります。

そこでこのページでは、誤差が出てしまう原因と、正確に時間計算する方法を解説していきます。

どういうときに誤差が出るのか

実は時間計算の誤差問題はエクセルに限った話ではなく、コンピューターを使った時間計算すべてで起きる可能性がある問題です。

具体的には、次の2つのケースで誤差が出ます。

1. 「時間計算した時間」と、「直接入力」したものを「比較」したとき
2. 「時間計算した時間」を「丸めた（切り上げ・切り捨て）」とき

それぞれ解説していきます。

1. 「時間計算した時間」と、「直接入力した時間」を「比較」したとき

下の図は、青く囲われた「直接入力した時間（8:00）」と、赤く囲われた「時間計算した時間」（退勤時間 – 出勤時間 – 休憩時間で計算）をIF関数で「比較」したときの図です。

IF関数では、赤く囲われたセルと、青く囲われたセルを比較し、時間が一致していたら「残業なし」、一致していなければ「残業あり」にしています。全て「残業なし」になるのが正解です。しかし結果は、正しくありません。

これはでは残業代計算したり、労務管理したりする場合に問題になる可能性があります。なぜ誤差が出るのかは後ほど解説していきます。

2. 「時間計算した時間」を「丸めた（切り上げ・切り捨て）」とき

下の図は、「退勤時間 – 出勤時間を15分で丸めた（切り捨て）」たときの図です。丸め後の時間は「7:00」が正解ですが、誤差のせいで黄色く塗ったセルは「6:45」になってしまっています。これは正しくありません。

このケースでも、やはり給与計算、残業代計算、労務管理で問題になります。上の図では15分の差が出てしまっていますので、時給1,000円だと250円の差です。このようなミスがある場合、労働基準法違反の可能性もあり、従業員から会社への信用がなくなります。

なぜ誤差が出るのかと、対策を知ることで正確で効率的に事務作業できるようになりましょう。

なぜ誤差が出るのか

誤差が出るのは、コンピューターは時間計算するとき「シリアル値で計算する」というルールに基づくからです。シリアル値とは1日を数字の1で表し、時刻を24で割った数値で表した数値です。例えば9:00のシリアル値は、「9 ÷ 24」した数値である「0.375」が9:00のシリアル値になります。

シリアル値にするとき、時刻が24で割り切れる数値であれば問題ないのですが、多くの場合、割り切れません。例えば4:00のシリアル値は「4 ÷ 24 =0.16666666666666……(無限に続く。循環小数といいます)」で割り切れない数値になります。これは分数 1/6が0.16666666666666……になるのと同じ理由からです。

いくらコンピューターが計算能力に優れているとはいえ、無限に続く数値を扱うことはできません。そのため、どこかで四捨五入します。

誤差の原因は、エクセルの計算能力の限界

一般的にエクセル内部では循環小数を15桁で四捨五入します。4:00の場合は0.166666666666667になります。実は、これが誤差を生む原因になります。

循環小数であっても、足し引き、丸め、比較しなければ問題ありません。具体的には、出勤時間・退勤時間などをセルに入力する場合は、なんら問題ありません。なぜなら時刻を直接入力したときの誤差は、実務上問題ない誤差だからです。

ところが、出勤時間・退勤時間を使って労働時間を計算したり、所定労働時間と比較したりするときに問題がでます。なぜなら誤差がある数値を使って計算すると、誤差が大きくなり、実務上問題が出るからです。

下の図の黄色く塗ったセルは、誤差が大きくなる例です。D列の「退勤時間 – 出勤時間」はすべて「7:00」です。しかし、E列の黄色く塗ったセルは最後の桁（15桁目）で誤差が出ています。

さらに、F列で丸め処理を行うとシリアル値が大きく変わっているのがわかります。これが、労働時間計算が狂ってしまう原因です。

こうした誤差は給与計算などに大きく関わるため、確実に修正しなければなりません。

関数を使って、セルへ直接入力したときと同じ状態にする

誤差を無くす最も簡単な方法は、TEXT関数とVALUE関数を使うやり方がおすすめです。

さきほど、「時刻を直接入力するときは、誤差が最小になり、実務上問題ない」と説明しました。この方法を使って正確な労働時間計算ができます。

TEXT関数とVALUE関数を使うことで、時間計算結果を、「時刻を直接入力」したときと同じシリアル値に修正することができます。

 

下の図は、時間丸めの誤差を、TEXT関数とVALUE関数を使って修正した例です。

TEXT関数とVALUE関数を使わなければ、誤差によっておかしな計算結果になります（黄色いセル）。しかしM列では、TEXT関数とVALUE関数を使い誤差を修正することで、正確な時間計算をすることができました。

TEXT関数とは

TEXT関数とは、「値」を「文字列に変換」する関数です。値は、上の例で「K2（退勤時間） – J2（出勤時間）」の時刻（シリアル値）を指定します。「表示形式」では、どのように書式設定するかを指定します。

労働時間計算では一般的に“h:mm“、計算結果が24時間以上になるときは“[h]:mm”と設定します。（書式設定の詳細ついてはこちらをご覧ください）

“h:mm”や”[h]:mm”と設定することで、計算によって求められた値を時間と分で丸め処理（切り捨て）します。つまり、誤差が生じる部分を切り捨て、「セルに直接入力」したのと同じことなります。

さきほど、「時刻を直接入力するときは、誤差が最小になり、実務上問題ない」と説明しましたが、TEXT関数を使うことでこの効果を得ることができます。

VALUE関数とは

VALUE関数とは、文字列を数値に変換する関数です。さきほど説明したように、TEXT関数だけでも計算補助機能があるため、問題ないことも多くあります。しかし計算補助機能は完全ではありません。具体的には、文字列と時刻を比較するときは補助機能が働きません。

下の図は、さきほども登場した「時間を比較するときの誤差」の図です。E列の残業有無ではIF関数を使って、所定労働時間（オレンジ色）に入力された「値」と「労働時間」を比較し、一致していれば「残業なし」、一致していなければ「残業あり」になります。全て「残業なし」が正解ですが、黄色く塗ったセルは「残業あり」と正しくありません。

誤差を修正するためにTEXT関数だけを使ったのがF列ですが、全て「残業あり」になってしまいました。これも正しくありません。原因は所定労働時間に入力された「値」と、TEXT関数で修正された「文字列」を比較したためです。このように、文字列と値を比較するときは計算補助機能が働かず、別物扱いになります。

そのため、G列ではTEXT関数で文字列に修正されたものを、VALUE関数でもう一度数値に変換します。こうすることで値同士を比較することになり、正しい結果になります。

 

応用編
VALUE関数の代わりに、TEXT関数に1を掛けても数値に変換できます。上の図を例にすると、「VALUE(TEXT(D5,”h:mm”))」と「TEXT(D5,”h:mm”)*1」は同じ結果になります。簡潔に書くことができるため、慣れてきたらこちらのほうがオススメです。

その他の方法

TEXT関数、VALUE関数を使って誤差を修正する以外の方法に、「シリアル値を10進法に変換し、誤差が生じない桁数で計算してから60進法に戻す」などの方法があります。

具体的には、分単位の精度を求めるのであれば、24(時間)×60(分)=1,440をシリアル値に掛けて計算した後に1,440で割り、再度シリアル値に戻します。秒単位の精度ならばさらに60(秒)を掛けて86,400を掛けて計算し、86,400で割り、シリアル値に割り戻します。

しかし、この方法は計算式が長く複雑になり、ミスが起きやすくなるためオススメできません。実務では正確性と効率が求められますので、一番簡単なTEXT関数とVALUE関数を使った方法がオススメです。

まとめ

労働時間計算を正確に行わなければ、給与計算や労働基準法遵守に影響します。エクセルで勤怠管理をする以上、時間計算誤差の問題は必ず生じる問題です。電卓で計算すれば時間計算誤差は生じませんが、効率的ではありません。TEXT関数とVALUE関数を使って正確に時間計算し、労務管理業務を効率的にすすめましょう。