勤務表の時間表示について

質問

エクセルで勤務表を作成しており、
月日・区分・始業時間・就業時間・休憩時間・所定時間・普通残業・時間外・深夜残業・・・と項目があります。
所定時間には7:30がマックス表示するようにしてあり、それを超えた7：31～8：00を普通残業に、さらに8：01～深夜残業に反映するようにしたいんです。
所定時間の表示と深夜残業の表示は出来たんですが、普通残業の表示がうまくいきません。
どなたか教えてください。

回答No.4

hirumin · ベストアンサー

> D7：就業時間
これは終業時間でしょうか。

以下の式に置き換えてみてはどうでしょうか？

F7：所定時間（7：30までしか表示しない）
=IF(B7>0,"",IF(D7-C7-E7>=7.5/24,7.5/24,IF(C7="","",D7-C7-E7)))
G7：普通残業（実働7：30を超え、8時間までの時間表示）
=IF(D7-C7-E7>7.5/24,IF(D7-C7-E7-7.5/24>=0.5/24,0.5/24,D7-C7-E7-7.5/24),"")
H7：時間外（実働8時間を超え、10時間までの時間表示）
=IF(B7>0,"",IF(D7-C7-E7>8/24,IF(D7-C7-E7-8/24>=2/24,2/24,D7-C7-E7-8/24),""))
I7：深夜残業（実働10時間を超えた場合の時間を表示）
=IF(B7>0,"",IF(D7-C7-E7>10/24,D7-C7-E7-10/24,""))
　　　　※単純に実働10時間以降で計算しています。

お礼

ありがとうございます。
完璧です。
出来ました。
助かりました。

回答No.5

shiotan99

No.3です。ちょっと確認ですが‥

> H7：時間外（実働8時間を超え、10時間までの時間表示）
> I7：深夜残業（実働10時間を超えた場合の時間を表示）

どういう勤務体制になっているのかわかりませんが、極端な例で、始業 22：00、終業 23：00だと実働 1時間ですが、現状の式では深夜残業 1：00 になると思います。それでいいのでしょうか？　要するに、実働 10時間を超えなくても 22：00を超えた勤務は無条件で深夜残業になるのでしょうか、ということなのですが‥

22：00を超えた勤務は深夜残業だとして、以下、No.3の補足説明です。
No.1さんへの補足にある時間外と深夜残業の式で、
IF(D7>"22:00", の "22:00" というのは単なる文字列ですから、時間データとしての数値に変換してやる必要があります。
エクセルは、文字列の数字を四則演算に使うと数値として認識するという仕様になっているので、"22:00"*1 として 1を掛けてやれば元の値を変えることなく数値に変換できます。

試しに C7に 9：00、D7に 23：00、E7に 2：00 と入れてみてください。
実働 12時間ですから 4時間30分の残業です。、
内訳は、普通残業 30分、時間外は 3時間、深夜残業 1時間になると思うのですが、現状の式では深夜残業が空白で、時間外は 4：00 になりませんか？（ もしかしたら #VALUE!エラーになるかも ）
IF(D7>"22:00"*1 としてやれば、ちゃんと時間外は 3：00、深夜残業は 1：00になります。
★ 時間外の IF(D7>"22:01" は "22：01" ではなく "22：00" だと思います。
四則演算に使えば、*1 とする必要はないので、D7-"22：00" はそのままでOKです。

◆ H7（ 時間外 ）
=IF(B7>0,"",IF(D7>"22:00"*1,D7-C7-E7-"8:00"-I7,IF(D7-C7-E7>8/24,D7-C7-E7-"8:00","")))

◆ ただし、上の式は↓のようにすれば短くできます。
=IF(B7>0,"",IF(D7-C7-E7>"8:00"*1,D7-C7-E7-"8:00"-N(I7),""))

※ 最後のほうの N(I7) の N 関数は文字列を 0にする関数。
もし I7セルの深夜残業が空白（ 数式「""」で空白 ）の場合、
D7-C7-E7-"8:00"-I7 とすると #VALUE!エラーになりますが、N 関数を使って N(I7)とすれば文字列（ 「""」での空白は文字列です ）は 0に変換されるので、ちゃんと計算できます。

◆ I7（ 深夜残業 ）
=IF(B7>0,"",IF(D7>"22：00"*1,D7-"22：00",""))

あと、残業が 0の場合は空白にするのでしょうか？
No.3 の式だと、0：00 になります。空白にするなら
<="7:30"*1,0, の 0を "" に変えてください。

◆
以下、別案です。よろしければお試しください。
実働時間の列を新たに追加すればこうなるという例です。
もちろん、これがベスト・アンサーだということではありません。
参考まで。
**< 前提 >*******************
・実働時間の列を追加（ F列 ）
・そのため列位置がズレます。
　所定時間が G列、普通残業が H列、時間外が I列、深夜残業が J列
・始業時間、終業時間で深夜0時を超える場合は、48：00表記で入力。
　深夜 1：00なら 25：00、3：30なら 27：30 というように。
　24：00以上でも表示できるように、始業時間、終業時間のセルの表示形式は、ユーザー定義で [h]：mm にしてください。
★ F1：=7：30、G1：=8：00、H1：=22：00 と入力しておく。
*****************************
以下をコピー＆ペーストしてください。

◆ F7（ 実働時間 ）
=IF(COUNT(C7:D7)<2,"",D7-C7-E7)

◆ G7（ 所定時間 ）
=IF(OR(B7>0,F7=""),"",MIN($F$1,F7))

※ ↑ F1セルに 7：30 と入っているとして。

◆ H7（ 普通残業 ）
=IF(OR(B7>0,F7=""),"",IF(F7<=$F$1,"",MIN(F7-$F$1,$G$1-$F$1)))

※ ↑ G1セルに 8：00 と入っているとして。

◆ I7（ 時間外 ）
=IF(OR(B7>0,F7=""),"",IF(F7>$G$1,F7-$G$1-N(J7),""))

※ ↑（ 実働時間 ）－ 8：00 － （ 深夜残業 ）という計算です。
　ここでも N 関数を使っています。

◆ J7（ 深夜残業 ）
=IF(OR(B7>0,F7=""),"",IF(D7>$H$1,D7-$H$1,""))

※ ↑ H1セルに 22：00 と入っているとして。

お礼

とても詳しく色々ありがとうございました。
ためになりました。

回答No.3

shiotan99

こんにちは～

> D7：就業時間

就業時間って、"終業時間" のことでしょうか？
そうだとすれば、実働時間（ =D7-C7-E7 ）を出す列を別につくっておいたほうが数式が簡単になると思いますよ。

とりあえず現状のままなら、普通残業は
◆ G7に
=IF(B7>0,"",IF(D7-C7-E7<="7:30"*1,0,MIN(D7-C7-E7-"7:30","0:30")))

でどうでしょうか？
"7:30" のあとの *1 をお忘れなく。

もし F1セルに 7：30 と入力されているのなら、
=IF(B7>0,"",IF(D7-C7-E7<=$F$1,0,MIN(D7-C7-E7-$F$1,"0:30")))

でもいいかもしれません。
　　◇
それと時間外、深夜残業の式ですが、これでホントにうまくいってます？
IF(D7>"22:01",
↓
IF(D7>"22:01"*1,

のように時間を "22:01" のようにして比較するときは、そのあとに *1 として数値化してやらないとダメだと思うのですが‥。

お礼

どうもありがとうございます。

もう少し検証をしてみますが、今のところ出来ましたヽ(^。^)ノ
休んだ日にも0:00と表示されるところがやや気になりますが・・・。

回答No.2

exceler405

=IF(B7>0,"",IF(D7-C7-E7>7.5/24,IF(D7-C7-E7-7.5/24>8/24,$G$1,D7-C7-E7-H7-I7)))
G1に 8:00 を入力しておく
でどうでしょうか？

回答No.1

mshr1962

所定時間=MIN(就業時間-休憩時間,"7:30"*1)
深夜残業=MAX(就業時間-休憩時間-"8:00",0)
ということでしょうか？
普通残業=IF(深夜残業>0,"0:30"*1,MOD(就業時間-休憩時間,"7:30"*1))

補足

mshr1962さん、すみません私の説明がやや足らなかったようです。

A7：月日
B7：区分（土日・祝日に1を入力）
C7：始業時間
D7：就業時間
E7：休憩時間
F7：所定時間（7：30までしか表示しない）
　　=IF(B7>0,"",IF(D7-C7-E7>7.5/24,$F$1,IF(C7="","",D7-C7-E7)))
G7：普通残業（実働7：30を超え、8時間までの時間表示）
　　※ここが上手く出来ません
H7：時間外（実働8時間を超え、10時間までの時間表示）
=IF(B7>0,"",IF(D7>"22:01",D7-C7-E7-"8:00"-I7,IF(D7-C7-E7>8/24,D7-C7-E7-"8:00","")))
I7：深夜残業（実働10時間を超えた場合の時間を表示）
　　=IF(B7>0,"",IF(D7>"22：00",D7-"22：00",""))

現状G7以外は上手くできてるんです。

宜しくお願いいたします。