Excel で労働時間を計算する: 夜勤、休憩、AI

打刻から打刻を引くことは、シフトが真夜中を越えたり、休憩が分単位で記録されたり、打刻漏れが誤解を招く数字に変わったりするまでは簡単に思えます。

24時間未満で、日付を含まない時刻だけの勤務記録では、日付をまたぐ時間差を計算し、休憩時間を引きます。打刻漏れは別に表示します。 その後、従業員IDごとに集計します。このGetSheetAIの開発者デモでは、100人分、3,000件の模擬勤怠データを使用しました。

完成したワークブックの内容

元の出席フィールドは残り、計算された時間とステータス列が追加されます。別の従業員概要には、合計時間数、有効な出勤記録、打刻漏れの記録が表示されます。ケースには 80 個の打刻漏れの行が含まれており、これらは何も表示されずに埋められるのではなく、修正のために表示されたままになります。

更新確認では、1件の勤務時間を7時間から8時間に変更しました。該当従業員の合計勤務時間も210時間から211時間に更新され、その後、元の値に戻しました。

勤怠データの準備

従業員 ID、名前、部門、勤務日、出勤時刻、退勤時刻、休憩時間を使用します。従業員を名前ではなく ID で識別します。クロック時間を Excel 時刻値として保存し、空白セルと午前 0 時を区別します。午前 0 時は正当な時刻値 0 です。

1 行が 1 勤務日を意味するのか、1 つの出勤間隔を意味するのかを決定します。従業員が 1 つの日付に複数の行を持っている場合、行のカウントでは個別の稼働日はカウントされません。この例の毎日の記録は、その概要を裏付けています。他のレイアウトには別のカウント ルールが必要です。

プラグインに通常のリクエストを与える

Excel のシートを開き、GetSheetAI を開いて、新しい会話を開始してください。このリクエストは次のように調整できます。

夜勤と休憩時間を差し引いた、勤怠記録ごとの労働時間を計算します。推測する代わりに、欠落している打刻値または打刻値にフラグを立てます。元のフィールドを保持します。時間を修正すると更新される数式を使用して、合計時間数、有効なレコード、および欠落したパンチのレコードを含む従業員の概要を作成します。

リクエストに IF、MOD、SUMIFS の名前を付ける必要はありません。これらの関数は、生成されたワークブックの結果の計算方法を検査するときに役立ちます。

時間の計算を理解する

C2 が出勤時間、D2 が退勤時間、E2 が休憩時間であるとします。英語の Excel 式の例は次のとおりです。

=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2/60)

欠損値チェックが最初に実行されます。 MOD は真夜中をまたぐ時間を処理し、24 を掛けると日が時間に変換され、休憩分を 60 で割ると同じ単位が得られます。 22:00 ～ 06:00 に 60 分の休憩を入れると、結果は 7 時間になります。午前0時を打刻漏れがあると誤解してはなりません。

このパターンは、24 時間未満の既知のシフトにのみ使用してください。同じ時計時間は、ゼロ時間を意味する場合もあれば、丸 1 日を意味する場合もあります。数式がそれを決めることはできません。複数日にわたるシフトの場合は、完全な開始日時と終了日時を使用します。負の計算結果、シフトよりも長い休憩、信じられないほど長いインターバルは、再検討のためにフラグを立てる必要があります。

例外を隠さずに従業員ごとに要約する

有効な打刻記録と欠落している打刻記録を区別するために別のステータス列を使用して、従業員 ID ごとに有効な時間を集計します。 SUMIFS は、従業員基準とステータス基準の両方を使用して時間を合計できます。 COUNTIFS は、同じ条件を満たすレコードをカウントできます。

記録された検証では、3,000 行のソース行、100 行の従業員概要行、夜間と深夜の両方のケース、すべてのソース値、および 80 行の打刻漏れフィルターがカバーされました。修正すると、詳細と従業員別の合計勤務時間が一緒に更新されます。

独自のレポートを再利用する前に、日中シフトを 1 つ、夜間シフトを 1 つ、有効な深夜時間を 1 つ、不完全なペアを 1 つチェックしてください。 1 回修正して、意図した従業員別の合計勤務時間が正確に変更されることを確認します。

よくある質問

これは給与計算または法定時間外労働を計算しますか?

このケースでは、所定のルールに従って出席時間を計算します。時間外労働のしきい値、有給休憩、休日割増金、四捨五入、夏時間の調整には別のルールが必要です。記録された例では、給与計算のコンプライアンスは検証されません。

来月の出席を追加できますか?

数式と集計範囲に新しいレコードが含まれていることを確認してください。 Excel テーブルはソース データの拡張に役立ちますが、すべての新しい行が含まれていると想定するのではなく、実際の参照を確認します。

時間がテキストとして保存されたらどうなるでしょうか?

まずソースを正規化します。 データ クリーニング ガイド では準備について説明しています。 AI数式ガイド は、計算を記述して検証する方法を示しています。