Excelを使った残業時間自動算出について

質問

社内の就業管理シートを作成しております。
色々調べてみたのですがExcelが使いこなせず困っております。。。
どなたかご教授をお願いします。

◎やりたいこと
画像にあります、
「通常残業時間数（Hの行）」と深夜残業時間数（Iの行）」の自動計算です。

会社の就労規定はざっくりとこのような感じです。
《就業時間》
・9:00－17:00
・就業時間8時間（休憩1時間含む）

そこで、以下の時間数を自動計算としたいと考えております。

●通常残業時間
18:00－22:00までの時間
（※該当時間がなければ空白にしたいです）

●深夜残業時間
22:00－5:00までの時間
（※該当時間がなければ空白にしたいです）

ドシロウトな質問ですみません。
どうかよろしくお願い致しますm(__)m

回答No.1

tsubuyuki · ベストアンサー

ＭＩＮ関数やＭＡＸ関数のちょっと便利な使い方、と言うのを考えてみます。

「＝ＭＩＮ（数値１，数値２）」と言う書き方をすると
「数値１と数値２で、小さい方」を返してくれます。
例えば、
　　=MIN(F1,"22:00")
と書いてやり、Ｆ１セルの数字（時刻）をいじってやります。
Ｆ１セルに「18:00」と入力すると、「18:00」が返り、
Ｆ１セルを「22:30」としてやると、「22:00」が返ってきます。

これを応用し、添付図のＨ１セルに、
　　式：=IF(F7<="18:00"*1,"",MIN("22:00",F7)-"18:00")
としてやると、Ｆ７が１８時以前なら空白、
１８時を過ぎたら「最大２２時まで」の超過時間（普通残業）時間を返します。

同様の考え方で、Ｉ１セルには
　　式：=IF(F7<"22:00"*1,"",MIN("29:00",F7)-"22:00")
としてやり、「２２時を超えて翌５時（２９時）までの分」を計算してやります。
（なので便宜上、Ｆ列には「２９：１５」と言う数字で打ち込んであります。）

以上、参考までに。

お礼

ありがとうございます！
とても早速入力してみたところうまく反映しました！
本当に助かりました。

回答No.2

KURUMITO

２４時を過ぎた時間は1:30のように入力するとします。
G７セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(E7:F7)<>2,"",IF(F7>E7,F7-E7-IF(F7-E7>="8:00"*1,"1:00",0),IF(F7<E7,F7+"24:00"-E7-IF(F7+"24:00"-E7>="8:00"*1,"1:00",0),"")))

H７セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(E7:F7)<>2,"",IF(AND(F7>E7,E7<"22:00"*1,F7>"18:00"*1),MIN(F7,"22:00")-MAX(E7,"18:00"),IF(AND(F7<E7,E7<"22:00"*1),"22:00"-MAX(E7,"18:00"),"")))

I７セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(E7:F7)<>2,"",IF(F7>E7,IF(AND(F7>"22:00"*1,E7<"24:00"*1),MIN(F7,"24:00")-MAX(E7,"22:00"),""),IF(F7<E7,MIN(F7+"24:00","29:00")-MAX(E7,"22:00"),"")))

お礼

ご丁寧にありがとうございます！
この方法でもできました！
ぱっとこういう式が作れる方々本当にすごいなと感服です。

本当にありがとうございました。