Excel。退勤-出勤で勤務時間を算出。翌日の深夜退勤でも簡単に算出する方法はないの？
＜SUM関数・四則演算＞
次のような表があります。
よくあるタイムカード計算の表でして、
D列に「退勤-出勤」を算出する式を作りたいわけですね。
当たり前ですが、退勤から出勤を減算すれば算出されますよね。
D3の数式には、
=C3-B3
では、オートフィルで数式をD6までコピーしてみましょう。
D4が##########となっていますね。
算出出来ていませんね。
つまり、夜勤がある会社の場合、退勤時間が、
日をまたいで、翌日に退勤となるわけです。
当然Excelは、翌日の6:00なんて認識はしてくれませんので、
6:00-22:00を減算したわけです。
表示形式が時間の場合に、
結果が基本的にマイナスだと#########と表示されてしまうのです。
では、いちいち、日付を比べてとか方法はあるのでしょうけれども、
簡単に計算する方法はないのかな？というのが、今回のご質問だったのです。
まずは、考え方として、翌日の6:00と認識してくれればいいわけですよね。
1日が24時間ですので、
24:00+6:00-22:00という計算式が作れれば良いということになりますよね。
しかし、なんでも24を加算するわけにはいきませんし、
個別で数式を修正していたら効率が悪いですし、
ミスを発生しやすくなってしますので、お勧めできません。
そこで、次のように数式を修正してみましょう。
D3の数式を、
=(C3<B3)+C3-B3
として、オートフィルを使って数式をコピーしてみましょう。
なんと、今度は、D4がきちんと算出されていますよね。
なぜ、
=(C3<B3)+C3-B3
という数式にしたら、算出できたのでしょうか？やっていること自体は簡単なのですが、
少しExcelのルールを知っている必要があります。
そのルールとは、「TRUEが1でFALSEが0」ということ。
まぁ、余談ですがあとは、1日は24時間ということ。
まぁ、こちらは当たり前ですね。
数式の説明です。
最初の(C3<B3)
これが何をしているのか？これがわかれば、あとは楽勝です。
C3は17:00、B3は9:00で、これを比較します。
当然、B3のほうが小さいのでこの(C3<B3)という条件は満たしていませんので、
結果はFALSEとなります。
先ほど書きましたが、FALSEはExcelだと「0」ということでしたので、
0+17:00-9:00=8:00
という結果が算出されたわけです。
では、#########と表示されていた、
D4は、なぜこの数式で算出できたのか？
C4<B4を確認すると、C4が6:00でB4が22:00なので、
この条件は満たされますので、TRUE。すなわち1。
1+6:00-22:00
1日は24時間ですから、
24:00+6:00-22:00=8:00
ということで、この数式で夜勤にも対応することができました。
ポイントは
C3<B3
という条件を使って、数値の1をもってくるというところですね。
1と0を持ってきたい場合には、
TRUEとFALSEというアイディアを知っておくと
色々便利になるかもしれませんね。
