Excelで勤務表を作りたいのですが

質問

Excelで勤務表を作りたいのですが、なかなか関数がうまく使えません。
時間計算をご存知の方、教えて下さい。

入力列
Ａ：出勤時刻
Ｂ：退勤時刻
Ｃ：勤務開始時間
*始業9:00のため、9時より以前の出勤時刻は9:00、9:00以降の出勤はその時刻を表示する。
Ｄ：休憩時間
Ｅ：実働時間（始：Ｃ/終：Ｂ/マイナス：Ｄ）
Ｆ：時間内勤務（８時間まで）
Ｇ：時間外勤務（８時間を超過したところから・２２時迄）
Ｈ：深夜残業（２２時以降の勤務時間）

このような説明でお分かり頂けるでしょうか。
よろしくお願いします。

回答No.2

keithin · ベストアンサー

順番に、その通り計算してみます。

＞Ａ：出勤時刻
＞Ｂ：退勤時刻

Ａ列とＢ列には「時：分」形式で時刻を記入する
０時を超えた時刻は２６：１５のように記入する

＞Ｃ：勤務開始時間
＞*始業9:00のため、9時より以前の出勤時刻は9:00、9:00以降の出勤はその時刻を表示する。

Ａ列が記入された時に計算する
９時と出社時刻の大きい方取り
=IF(A2="","",MAX(A2,TIME(9,0,0)))

＞Ｄ：休憩時間

ご相談範疇外
条件に応じて休憩時間を産出する
たとえば出社時刻が１２時前かつ退社時刻が１３時を超えたら、など。
それ以外は０：００を計算しておく

＞Ｅ：実働時間（始：Ｃ/終：Ｂ/マイナス：Ｄ）

始業時刻、退社時刻が記入された場合に計算する
=IF(COUNT(A2:B2)=2,B2-C2-D2,"")

＞Ｆ：時間内勤務（８時間まで）

実働時間が計算された場合に計算する
８時間と実働時間数の小さい方取り
=IF(E2="","",MIN(TIME(8,0,0),E2))

＞時間外勤務（８時間を超過したところから・２２時迄）

実働時間が計算された場合に計算する
=IF(E2="","",MAX(0,MIN(TIME(22,0,0),B2)-C2-D2-TIME(8,0,0)))

＞Ｈ：深夜残業（２２時以降の勤務時間）

実働時間が計算された場合に計算する
=IF(E2="","",MAX(0,B2-TIME(22,0,0)))

お礼

おかげさまで理想的な勤務表が作成できそうです！
煮詰まって途方に暮れていたので、本当に助かりました。
つたない質問に回答をして頂けるか心配でしたが、御丁寧に教えて下さり
ありがとうございました。

回答No.1

MarcoRossiItaly

時間のセルの書式は全て、「h:mm」ではなく、「[h]:mm」に設定してあります。日付が変わって退勤した場合は、「25:00」という具合に実績を記入します。

なお休憩時間の長さは、退勤時刻が 13:00 以後である行は 1:00、そうでない行は 0:00 を算出するようにしてみました。

A2　出勤時刻の実績値
B2　退勤時刻の実績値
C2　=max(a2,"9:00")
D2　=(b2>="13:00"+0)*"1:00"
E2　=b2-c2-d2
F2　=min(b2,"17:00")-c2-d2
G2　=max(,min(b2,"22:00")-max(c2,"17:00"))
H2　=max(,b2-"22:00")

＞Excelで勤務表を作りたいのですが、なかなか関数がうまく使えません。

関数あるいは数式と言う前に、時間や日付のシリアル値についての様々な知識を得ることが先決です。時間などが記入されているセルの書式を「標準」などに変更した際に表示される小数が、その時間に対応するシリアル値です。

お礼

私のつたない説明に、大変丁寧に教えて頂いてありがとうございました！
工夫によって、シンプルな式で作れるものなんだと感動しました。
時間や日付を余り扱うことがないため、ごちゃごちゃ考え過ぎてしまいました。
おっしゃる通り、ちゃんと知識がベースにないと限界がありますね・・・
もうお一人の御回答が理想に近かったので、ベストアンサーを差し上げられなくて申し訳ありませんが、
本当にありがとうございました。