Excelでの勤怠表の関数を教えてください

Excelで日報を作成しています。
始業時間・終業時間・休憩時間を入れて8時間以内の勤務の場合は

（定時間内）
IF(D4-C4-E4>=TIME(8,0,0),TIME(8,0,0),D4-C4-E4)
で表示させました。

（定時間外）8時間以上勤務の残業は
IF(D4-C4-E4>TIME(8,0,0),D4-C4-E4-TIME(8,0,0),"")
で表示させました。

あと、以下わからないので教えてください。
・日曜日に勤務した場合に休日出勤のところに表示をさせたいのですが、
どのような関数になりますか？
・22時以降の残業の場合、深夜残業に表示させたいのですが、どのような関数になりますか？
・8時間以内の勤務でも22時以降となる場合（例えば例でいうと5日の勤務です）、22時以降は定時間内深夜に入れたいのですが、どのような関数になりますか？

トータルで考えると（定時間内）、（定時間外）の関数も間違えてしまっているかもしれませんが、まったく関数については素人の為、ぜひご教示いただけましたら嬉しいです。

宜しくお願い致します。

質問者からの補足コメント

• ※申し訳ありません。エクセルの画像がエラーで添付できていませんでした。
  添付しますので宜しくお願い致します。

  
    補足日時：2024/09/30 21:32

No.2 ベストアンサー 回答者： grumpy_the_dwarf 回答日時： 2024/09/30 16:00

IF(D4-C4-E4>=TIME(8,0,0),TIME(8,0,0),D4-C4-E4)

上限を定めるならmin関数一発
=min("8:00", D4-C4-E4)

IF(D4-C4-E4>TIME(8,0,0),D4-C4-E4-TIME(8,0,0),"")
下限を定めるならmax関数一発
=max(0, D4-C4-E-"8:00")

出勤(C4)から退勤(D4)までのうち深夜帯(22:00〜29:00)に該当するのは
=max(0, min(D4*1, "29:00")-max(C4, "22:00"))

出勤の8時間+休憩時間後から退勤までつまり超過勤務のうち深夜帯に該当するのは
=max(0, min(D4*1, "29:00")-max(C4+E4+"8:00", "22:00"))

全て表示形式は [h]:mm;; とする。それでゼロが消えるので無意味なif関数を一掃できる。

この回答へのお礼

grumpy_the_dwarf様
色々と教えていただき有難うございます。すぐに計算出来て、とても分かりやすかったです。あと、添付のExcelが表示できていなかったのですが添付し直しました。
例えば、4日の部分で、定時間外4：30、深夜残業1：00となる方法、5日の部分では定時間内2時間、定時間内深夜1：00となる方法も教えてください。宜しくお願いいたします。

お礼日時：2024/10/01 18:00

No.5 回答者： grumpy_the_dwarf 回答日時： 2024/10/02 17:38

所定時間内の深夜、所定時間内の深夜以外、と細かく分けるのは意味がありません。

時間外が7時間で25%増し*7、深夜が1時間で25％増し*1、ですみます。重複してても関係ありません。

ただ考え方としては、「出勤時刻+休憩時間+8時間」が所定外時間の始まりで、そこから退勤までのうち深夜の22時から29時に該当する時間と深夜ではない5時から22時に該当する時間をそれぞれ計算することになるでしょう。

No.4 回答者： mike_g 回答日時： 2024/10/01 18:42

[No.3お礼]への些末な質問、

遅ればせ添付図、多謝です。
今回のご質問とは関係ないかも、ですが、時間稼ぎのための余計な確認です。
右端の[定時間内深夜]とはどういう意味ですか？私の貧弱な推理力では難解です。(*^_^*)

この回答へのお礼

mike_g様
ご返信有難うございます。定時間内深夜とは定時間8時間だけど、22時以降に勤務しているということです。21：00～24：00だと3時間勤務ですが、22時以降は深夜の割増料金が発生するので1時間は通常勤務、2時間分は深夜の割増料金分として別のセルに入れたいのです。
宜しくお願いいたします。

お礼日時：2024/10/02 15:42

No.3 回答者： mike_g 回答日時： 2024/09/30 16:21

とぅら さん、

貴方が提示した式↓
=IF(D4-C4-E4>=TIME(8,0,0),TIME(8,0,0),D4-C4-E4)
を使用する際の、セル D4、C4、E4 の数値と意味(何の時刻か?)を教えてください。
その方が式を理解する上で助かります。

この回答へのお礼

mike_g様有難うございます。Excel画面が添付出来ておらず添付いたしました。ぜひご教示お願いいたします。

お礼日時：2024/10/01 17:56

No.1 回答者： fujillin 回答日時： 2024/09/30 01:03

こんばんは

セルの配置が不明なのでよくわかりませんし、回答も考え方のみなってしまいますが・・


＞日曜日に勤務した場合に休日出勤のところに表示をさせたいのですが
どこかに日付（＝シリアル値）の入力されているセルがあると思います。
そのセル値を利用して判断すれば良いでしょう。
Weekday関数で曜日を算出できますので
　=IF(WEEKDAY(日付)=1, 表示したい値,"")
とでもしておけばよさそうです。

＞22時以降の残業の場合、深夜残業に表示させたいのですが～
「休憩時間」というのが固定時間帯なのか可変なのかによって異なります。
もしも休憩時間に22時以降と重なる時間帯がある場合は、その時間を減算しなければなりませんので・・
仮に、12:00～13:00のように固定で、重なることが絶対にないと仮定しても良いのなら、
　=MAX(終業時刻 -TIME(22,0,0),0)
としておけば計算できます。
「0」表示が嫌なら、
　=IF(終業時刻 >=TIME(22,0,0),終業時刻 -TIME(22,0,0),"")
等としておけばよいでしょう。
ただし、22:00以降の終業時刻の場合で、24:00を超える時刻に関しては25:00のような入力にしておく必要があります。
（あるいは、○時以前の終業時刻は深夜をまたぐと判断して、1日を加算するなどの対処をするとか）

＞8時間以内の勤務でも22時以降となる場合（例えば例でいうと5日の勤務です）
「例」というのがどこにあるのか不明ですけれど・・
上記で、22時以降の時間が計算できますので、「8時間以内か否か」の判断条件を付け加えれば宜しいのではないでしょうか？

※ 式中の「時刻」、「日付」は全てシリアル値（＝エクセルの時刻型、日付型の値）であるものと仮定しています。

この回答へのお礼

fujillin様有難うございます。Excel画面が添付出来ておらず添付いたしました。ぜひご教示お願いいたします。

お礼日時：2024/10/01 18:01