残業17:30～22:00と深夜残業22:00～5:00の１日ごとの残業時間集計を数式で計算したい

残業時間17:30～22:00のE2に=D2-"17:30"の計算式を入れてみました。
しかし、30分を0.50と表示させたいですが、0.30と表示されてしまいます。
又、空欄にしたい箇所が、###########になってしまいます。
下記の画像をご参照ください。

それぞれの残業欄にどのような計算式を入力すればよろしいですか？
よろしくお願いします。

No.1 ベストアンサー 回答者： zongai 回答日時： 2021/11/18 22:15

この残業計算表って、開始時間は全く考慮しないのですね。

さてさて、

・Cが空白なら空白にする。
・17:30以降は残業となる。
・「残業」は最大でも4.5時間
・「深夜残業」は最大でも7時間

その条件で式を作るなら、

E2
=IF(C2="","",IF(D2>"17:30"*1,MIN(4.5,(D2-"17:30")*24),""))

F2
=IF(C2="","",IF(D2>"22:00"*1,MIN(7,(D2-"17:30")*24),""))

※E列、F列は「セルの書式設定」で小数第2位までの数値として下さい。

注意しなければならないのは、
終了時間が0:00を過ぎたら、「24:00」「24:30」と入力しないと計算が正しく行われないこと。

計算式の方で対応させるなら、
・終了時間が開始時間より早い時間なら終了時間に1日を足して計算する
という処理を追加して

E2
=IF(C2="","",IF(IF(D2<C2,D2+1,D2)>"17:30"*1,MIN(4.5,(IF(D2<C2,D2+1,D2)-"17:30")*24),""))

F2
=IF(C2="","",IF(IF(D2<C2,D2+1,D2)>"22:00"*1,MIN(7,(IF(D2<C2,D2+1,D2)-"22:00")*24),""))

※E列、F列は「セルの書式設定」で小数第2位までの数値として下さい。

これなら、午前1時を「25:00」と入力しても「1:00」と入力しても対応可能です。
とりあえず、質問に書かれていることについてはこれでOKでしょう。
（やりかたは人それぞれで他にも方法はあります）

この回答へのお礼

早急なご回答、ありがとうございました。
こちらの計算式を入力して思い通りの残業時間計算が出来ました。
とても助かりました。

お礼日時：2021/11/19 13:51

No.3 回答者： トモクンアヤチャン 回答日時： 2021/11/19 10:42

E2=IF(D2="","",

IF(AND(D2>TIMEVALUE("08:30"),D2<=TIMEVALUE("17:30")),0,
IF(OR(D2>=TIMEVALUE("22:00"),D2<=TIMEVALUE("08:30")),4.5,
HOUR(D2-"17:30")+MINUTE(D2-"17:30")/60)))

F2=IF(D2="","",
IF(AND(D2>TIMEVALUE("08:30"),D2<=TIMEVALUE("22:00")),0,
IF(D2>TIMEVALUE("22:00"),HOUR(D2-"22:00")+MINUTE(D2-"22:00")/60,HOUR(D2+"02:00")+MINUTE(D2+"02:00")/60)))
ではどうでしょうか。
問題があります。完全徹夜した場合、終了時間が翌日の終了時間になってしまい、正しく計算できません。一応、終了時間が翌日8：30までは正しいと思われます。

この回答へのお礼

このような複雑な計算式もあるのですね。勉強になります。
丁寧に教えて頂き、ありがとうございました。

お礼日時：2021/11/19 13:55

No.2 回答者： mike_g 回答日時： 2021/11/19 07:29

》 =D2-"17:30"の計算式を入れてみました。

》 しかし、30分を0.50と表示させたいですが、
》 0.30と表示されてしまいます。

その原因は、その式を入力したセルの書式が h:mm に自動設定されているから、ですよね。その式を =(D2-"17:30")/"1:0" に、書式を G/標準 に、それぞれ変更したら如何？

この回答へのお礼

計算式　=(D2-"17:30")/"1:0"を入力して書式設定を ユーザー定義の0.00に変更したら出来ました。
ありがとうございました。

お礼日時：2021/11/19 13:54