Excelでは時刻の計算もできるので、タイムカードを作ることは比較的容易です。 退勤時間から出勤時間を差し引き、そこからさらに決まっている休憩時間を差し引けばいいのですから。 しかし、土日祝日出勤、特別休暇、深夜残業、フレックスタイムなど、複雑な要素が絡むと途端に難しくなります。 そこで、今回は、そんな複雑な時にも使える計算方法のアイデアを紹介します。
時刻の15分おきの切り捨て、切り上げ
出勤、退勤時間を実際の時間で記録する場合、例えば8:55出勤なら9:00出勤、18:03退勤なら18:00退勤と計算するケースが多いと思います。 例えば出勤時間は15分単位で切り上げるのであれば、 =CEILING(出勤時間,TIME(0,15,0)) です。 退勤時間は15分単位で切り上げるのであれば、 =FLOOR(出勤時間,TIME(0,15,0)) です。 30分おきなら計算式の15を30に変えてください。 1時間おきならTIME関数をTIME(0,60,0)でもいいですし、きちんと1時間とTIME(1,0,0)で記述してもよいです。 ※将来、CEILING関数、FLOOR関数は、使えなくなり、それぞれCEILING.MATH、FLOOR.MATHという関数名で使うようになります。
その時間にいたかの判定
例えば、深夜残業時間帯にいたかどうかを判定するには、その時間帯にいたかどうかを判定すればよいです。 15分単位にいたかどうかを判定する方法を紹介します。
このように出勤時間を退勤時間があって、単純に合計時間を求めます。
一般的には早朝出勤のスタートは5:00ですので、5:00から始めます。
セルD1に「5:00」と入力します。
セルE1に「=D1+TIME(0,15,0)」と計算式を入れます。
そしてこのE1に入力した計算式を右方向に、セルCV1までコピーします。
ここまでで、翌日の5:00までの15分おきの時刻が出来上がります。
セルD2に「=IF(AND($A2<=D$1,$B2>=E$1),1,0)」と計算式を入力し、セルCU2まで右方向にコピーします。
これで、その15分にいたら2行目に1と表示されます。
セルC2に「=SUM(D2:CU2)* TIME(0,15,0)」と入力すれば、すべてのいた時間の合計が表示されます。
計算したい時間帯のみSUM関数を使えばいいですね。22:00から右全部の合計なら、深夜残業時間内の就業時間になります。
この2行目の計算式は、絶対参照を考慮してあるので、行ごと下にコピーするだけでその行の勤務時間が計算できます。
今回の方法はタイムカードだけではなく、稼働時間の分析にも使えます。
15分ごとではなく、もっと厳密に1分ごとに計算する時も手法は同じです。
その場合、計算速度は遅くなるので注意しましょう。
コメント