【Excel2003】時間帯で労働時間を割り振る方法

質問

【Excel2003】時間帯で労働時間を割り振る方法

添付画を添えておきますのでご覧下さい。

画左上のように時間帯が３つあります。
・時間内
・時間外1
・時間外2　の3種類です。

時間内は　 =H9-J3　なので簡単です。
しかし、時間外1と時間外2に入れる計算式が分かりません。

条件として・・・
時間外1は、上限4：00です。
時間外2は、22：00以降の労働時間が入ります。

※勤務時間には、休憩時間（J2）が考慮されています。

簡単に出来ると思いきや行き詰ってしましました。
どうぞよろしくお願いいたします。

回答No.5

KURUMITO · ベストアンサー

Ｈ9セルには次の式を入力します。

=IF(COUNT(F9:G9)<>2,"",G9-F9-"1:00")

Ｉ9セルには次の式を入力します。

=IF(H9="","",MIN(G9,"18:00")-MAX(F9,"9:00")-"1:00")

Ｊ9セルには次の式を入力します。

=IF(H9="","",IF(MIN(G9,"22:00")>=MAX(F9,"18:00"),MIN(G9,"22:00")-MAX(F9,"18:00"),"0:00"*1))

Ｋ9セルには次の式を入力します。

=IF(H9="","",IF(G9>=MAX(F9,"22:00"),G9-MAX(F9,"22:00"),"0:00"*1))

最後にＨ9セルからＫ9セルまでを選択し、それらの式を下方向にオートフィルドラッグします。

お礼

ご回答ありがとうございました。

求めていたものはこれですね。
時間内勤務は9:00から計算しなければいけなかったのです。

助かりました。
また機会があればよろしくお願いいたします。

回答No.4

MackyNo1

No3の回答ですが、訂正です。

H9セルは休憩時間を除いた勤務時間を求めるI9セルの間違いです（以下1つずつずれる）。

回答No.3

MackyNo1

各セルに以下の数式を入力し（適宜セルの書式を時刻にして）下方向にオートフィルしてください。

H9セル
=MIN(G9,18/24)-MAX(F9,9/24)-1/24

I9セル
=MAX(9/24-F9,0)+MAX(G9-18/24,0)

J9セル
=MAX(G9-22/24,0)

補足

ご回答ありがとうございます。

時間外1の計算が・・・
あれ？どうしたらいいのかな？

回答No.2

keithin

基本形：
（丸写しのコピーではなく基本の理解のため，休憩は必ず入る，夕方から出勤などイレギュラーは考慮していない，など）

H9:
=IF(COUNT(F9:G9)=2,G9-F9-$J$2,0)
セルの書式設定は　[h]:mm;;;

I9:
=MIN(H9, "8:0")
セルの書式設定は　[h]:mm;;;

J9:
=MIN(MAX(0, I9-"8:0"),"4:0")
セルの書式設定は　[h]:mm;;;

K9:
=MAX(0,H9-I9-J9)
または
=MAX(0,H9-"22:0")
セルの書式設定は　[h]:mm;;;

回答No.1

bin-chan

「正午から出勤する」ようなパターンは無いものとして、例外を考慮しません。また、試験してません。

13行目とします。
最低でもi13="8:00" の時、時間外計算するものとしてます。

時間外1
セルJ13に式[=if(and(i13="8:00",G13>"18:00"),"22:00"-G13,"")]
時間外2
セルK13に式[=if(and(i13="8:00",G13>"22:00"),G13-"22:00","")]