エクセルでの早出・残業管理表の作成

質問

こんにちは。

エクセルを使い、「早出・残業時間管理表」を作成したいのですが、イメージしているものが出来ず困っています。

次のような内容を考えています。

「Ａ1に日にち」・「Ｂ1に曜日」・「Ｃ1に出勤した時刻（例6：45）」・「Ｄ1に定時出勤時刻（7：15が定時）」
「Ｅ1に早出出勤分の時間数（例の場合は３０分）」

同じ内容で残業時間も計算したいと考えています。

早出・残業の定義は・・・
出勤の定時は「7：15」・帰りの定時は「16：45」
7：15より前を早出時間・16：45以降を残業時間とします。

早出・残業を何時間したかを１ヶ月分計算したいのです。

ひとつ難しいのは、7：15より遅く出勤した場合と、16：45より早く帰った場合です。
この場合、その日を「-○○分」とし、１ヶ月の合計時間から引いて計算したいのです。

説明が足らず分かりづらいと思いますが、詳しい方、ご教授お願い致します。

回答No.2

KURUMITO · ベストアンサー

時刻をマイナス表示にすることは通常はできません。
次のような表にして管理すればよいでしょう。
例えばA1セルには日付、B1セルには曜日、C1セルには出勤時刻、D1セルには退社時刻と文字を入力し、また、E1セルには早出時間、F1セルには残業時間、G1セルには遅出時間、H1セルには早退時間とでも入力します。遅出時間は7:15よりも遅く出勤した時間、早退時間は16：45よりも早く帰った場合です。
E2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(C2:D2)<>2,"",MIN(D2,"7:15")-MIN(C2,"7:15"))

F2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(C2:D2)<>2,"",IF(D2<"16:45"*1,0,D2-"16:45"))

G2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(C2:D2)<>2,"",IF(C2>"7:15"*1,C2-"7:15",0))

H2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(C2:D2)<>2,"",IF(D2<"16:45"*1,"16:45"-D2,0))

そこで一カ月の集計ですがI1セルには早出時間集計、J1セルには遅出時間集計とでも入力して、I2セルには次の式を入力します。

=SUM(E:E)-SUM(G:G)

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

=SUM(F:F)-SUM(H:H)

なお、いずれの列もセルの表示形式は時刻にします。
また、シートは一カ月単位で入力されているとします。

お礼

早速の返信、ありがとうございます。

これ以上ない分かりやすい内容で、解決致しました。

自分のイメージ通りに作成できてうれしく思っております。

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

回答No.1

keithin

＞この場合、その日を「-○○分」とし、１ヶ月の合計時間から引いて計算したい

このアイデアが，かえって計算を難しくしています。
淡々と毎日の実績勤務時間数を求めて一月分を合計し，更にもしも必要なら勤務日数×所定１日時間数で所定の当月勤務時間数を算定して比較してみてください。

Ａ列に日付
Ｂ列に曜日
Ｃ列に出社時刻
Ｄ列に退社時刻

定時の出社時刻は7:15
定時の退社時刻は17:30
定時の昼休みは12:00から13:00
とすると

Ｅ列に早出時間数の算定（0:00～7:15)
=MAX(MIN("7:15",D2),"0:00")-MIN("7:15",MAX("0:00",C2))
Ｆ列に午前の勤務時間数の算定（7:15～12:00）
=MAX(MIN("12:00",D2),"7:15")-MIN("12:00",MAX("7:15",C2))
Ｇ列に午後の勤務時間数の算定（13:00～17:30）
=MAX(MIN("17:30",D2),"13:00")-MIN("17:30",MAX("13:00",C2))
Ｈ列に残業の勤務時間数の算定（17:30～24:00）
=MAX(MIN("24:00",D2),"17:15")-MIN("24:00",MAX("17:15",C2))

といった具合です。
ご相談で未だ考慮されていない深残時間帯の算定とか，日付をまたいで勤務した場合の取り扱いなども，適宜応用して組み込んでください。