日またぎにも対応した勤務時間表を作りたい（やや長文）

質問

現在、Excelで勤務時間表を作っています。
使用者が始業時刻と終業時刻を入力すると後は全て計算で出るように作っています。
誤入力防止の為、入力規則で0:00～23:59しか入力出来なくしています。
(深夜1時は25時ではなく1時と入力）
通常時間帯と深夜時間帯では手当が異なるのでそれぞれの勤務時間を算出します。
深夜時間帯は22：00～6：00です。
早く出勤した場合の早出はMAX(6:00－[始業時間])で対応出来ました。
日をまたがない場合はMAX([終業時刻]－22:00)で対応出来るのですが
日をまたいだ場合、深夜時間帯の労働時間算出がうまく出来ません。
誤入力を考慮せず25:00と入力させた時はうまく出来ました。
（終業時刻入力形式を0:00～6:00は入力制限していた。
但し、整数を入れるとそのまま24:00、48:00と表示されてしまったため使用中止）
現在の入力形式のままでうまく機能させる式、
或いは、終業時刻の入力をうまく制限する方法がありましたら教えて下さい。

回答No.2

shiotan99 · ベストアンサー

こんにちは～

始業時刻が、22：00以降ということはないのでしょうか？
また、深夜残業の終了時刻が 6：00 を超えたらどうするのでしょうか？

そういうことは考慮しなくてもよければ、
「始業時刻」が A2、「終了時刻」が B2 だとして、

=MAX(B2+(A2>B2)-"22:00",0)

で試してみてください。

お礼

回答ありがとうございます。
教えて頂いた式でうまく出来ました。

ただ、上司より勤務時間の概念上はやはり
AM1:00は25:00と表示した方が分かりやすいと
言われてしまったので、結局元の式に戻す事となりました。
でも、頭で理解している事を式化出来ずにいたので
非常に助かりました。ありがとうございます。

回答No.1

nonpq

ExcelやAccessで日付を管理するのにプログラム上では、日付シリアルが用いられていることをご存知ですか？整数部分を日付、少数以下が時間となります。
出退勤時刻は多分日付と分離して時刻だけを入力するのが一般的だと思います。ということは、0.xxxxxxというシリアル値が入力されているわけです。ので、整数部の日付をシリアル値として時刻シリアルに加算することで、日付またぎが可能となるのです。
ちょっと分かりにくい説明ですね。
ｅｘ）
　Ａ１　　Ｂ１　　　　Ｃ１
　日付　出勤時刻　　退勤時刻
　9/2 　　7:00　　　　1:00　（表示形式：日時）
　 ↓　　　↓　　　　　↓
　38597　0.291667　 0.041667（表示形式：標準）
9/2 7:00のシリアル値は38597.291667（A+B列）
9/2 25:00（9/3 1:00）のシリアル値は38598.041667
（A列+1+C列）となるわけです。
あとは退勤時刻が23:59（0.999306）より大小かをネストして計算すればＯＫなのでは？