勤怠管理で始業時刻を基準に計算させるには？ エクセルで勤怠管理表を作成するにあたって、例えば8時30

勤怠管理で始業時刻を基準に計算させるには？
エクセルで勤怠管理表を作成するにあたって、例えば8時30分が始業時刻とする場合、社員は8:05にタイムカードに記録した場合や7:58などに記録した場合は、始業時刻より余計な時間分も勤務時間を計算する上でふくまれてしまいます。とにかく8:30より早く出勤する場合でも、管理上セルの値は8:30になるように変換したいです。また、万一、7:20に記録した場合をセルに入力した場合は、1時間単位の7:30に変換したいです（朝1時間、時間外勤務を計算させるため）。
社員が全員、8:30に記録してくれたら、エクセル上の計算はたやすいのですが、出勤時間は皆、バラバラなので始業時間前の何分かも勤務時間を計算するときに余計に加算されるため、あくまでも始業時刻の8:30を基準に計算させるためには、関数式をどう言う風に工夫すればよいですか？
それとも、関数式では無理ですか？なお、終業時刻についても同じ理屈です。
よろしくお願いします。

No.3 ベストアンサー 回答者： tefu_tefuu 回答日時： 2024/01/06 05:21

1時間とか30分とか切り捨てると、59分とか29分とかの切り捨てになるので、

テキストでは、15分単位で、CEILING関数（基準値の倍数の内、最も近い値に切り上げる）とFLOOR関数（基準値の倍数の内、最も近い値に切り捨てる）を使うみたいですよ。
＝CEILING(A2,1/96)
＝FLOOR(B2,1/96)
上の式を横の列に別表にすると、訂正された数値が判断できます。

シリアル値では、
A2→CEILING(A2,1/96)
B2→FLOOR(B2,1/96)

=IF(COUNT(A2:B2)<2,"",FLOOR(B2,1/96)-CEILING(A2,1/96)-MAX(0,MIN(77/144,FLOOR(B2,1/96))-MAX(1/2,CEILING(A2,1/96)))-MAX(0,MIN(91/144,FLOOR(B2,1/96))-MAX(5/8,CEILING(A2,1/96))))

時刻をそのまま使う場合は、
A2→CEILING(A2,"0:15")
B2→FLOOR(B2,"0:15")

=IF(COUNT(A9:B9)<2,"",FLOOR(B9,"0:15")-CEILING(A9,"0:15")-MAX(0,MIN("12:50",FLOOR(B9,"0:15"))-MAX("12:00",CEILING(A9,"0:15")))-MAX(0,MIN("15:10",FLOOR(B9,"0:15"))-MAX("15:00",CEILING(A9,"0:15"))))
どちらの場合も、表示形式はｈ：ｍｍ

この回答へのお礼

ありがとうございます。なるほどよくわかります。一時、ROUNDとかもら考えましたが、うまくいかず、この関数なら上手くいきました。本当にご親切な方で感謝致します。ありがとうございました。

お礼日時：2024/01/06 08:29

No.2 回答者： fujillin 回答日時： 2024/01/04 22:38

No1です。

＞遅刻など時間がとれなくなるので、困っています。
シリアル値を用いているのなら、max関数で遅い方を採用すれば良いだけでしょう。

この回答へのお礼

ありがとうございます。
>MAX関数の遅い方
がよくわかりませんが、繰り上げ繰り下げなども考えましたが私のレベルでは難解です。ありがとうございました。

お礼日時：2024/01/04 23:32

No.1 回答者： fujillin 回答日時： 2024/01/04 21:51

こんばんは

ご質問の意味がよくわかりませんけれど・・

「入力値が何時になっていようが、8:30と見做したい」というように読めます。
であるなら、入力値のセルを参照する代わりに、"8:30"の固定値を使用すれば良いだけでしょう。

「勤怠管理」とのご質問ですが、限りなくブラックな臭いがしますね・・

この回答へのお礼

ありがとうございます。固定値にしてしまうと、遅刻など時間がとれなくなるので、困っています。例えば8:00.〜17:00で8時間勤務なら、実際は7:50入〜17:10出なら勤務時間8時間のところ、計算上、勤務時間が8時間20分になってしまうと言うことです。

お礼日時：2024/01/04 22:12