Googleスプレッドシートで作る勤務管理表ー数式を使って残業時間を計算ー

2024年10月4日2024年10月6日

スプレッドシートを使えば効率的に勤務管理表を作成できますが、時間の表示形式や残業時間の計算式に悩むこともあったりしませんか？

勤務管理表の作成に時間をかけすぎるのはもったいないですし、その時間を他の業務に充てたいところです。

そこで、この記事ではスプレッドシートを使った勤務管理表の作成方法を紹介します。

残業時間の計算や時間の表示形式の設定をサンプルを使ってお伝えします。

完成イメージ

勤務開始・終了時間を入力すると、残業時間が計算される　← 今回はこの部分
年月を選択すると、日付が入力される
土日・祝日の行は色分けされる

この記事では残業時間の計算についてご紹介します。

日付の自動入力や土日祝日の色分け設定については以下の記事をご参照ください。

あわせて読みたい

スプレッドシートで日付を自動入力！年月を設定するだけで日付リストを自動作成する方法この記事をおすすめの方スプレッドシートでの反復作業を減らして時短したい方毎月のカレンダーや勤怠表を手作業で作成している方スプレッドシートでカレンダーや勤怠…

あわせて読みたい

スプレッドシートで土日祝日を自動色分け！祝日リストもGASでかんたん自動作成スプレッドシートで日付入力の際、土日祝日のセルを自動的に色分けしたい時ありませんか？セルを手動で色分けとなると、毎回の作業がとても面倒ですよね。自動的に色…

勤務管理表の作成方法

STEP

基本項目の設定

まず、管理表に必要な基本項目を設定します。

例として以下の項目をスプレッドシートに入力します。

A6：日付
B6：祝日
C6：開始時間
D6：終了時間
E6：定時開始時間
F6：定時終了時間
G6：始業前残業
H6：終業後残業
I6：深夜労働時間
J6：休日労働時間
K6：有給休暇
L6：残業時間計

STEP

数式の設定

次に、各項目を自動で計算する数式を設定します。

G列：始業前残業
H列：終業後残業
I列：深夜労働時間
J列：休日労働時間

始業前残業の計算式（G列）：

=IF(ISBLANK(C7), 0, IF(C7 < E7, (E7 - C7) * 24, -(C7 - E7) * 24))

この数式は、開始時間（C列）と定時開始時間（E列）の差を計算し、始業前残業時間を求めます。

数式の説明

IF(ISBLANK(C7), 0, ...) の部分では、もし開始時間が入力されていない場合（空白の場合）、残業時間を0とします。
開始時間が入力されている場合は次の条件に進みます。
C7 < E7 は、「開始時間が定時開始時間よりも早いかどうか」を判断しています。
もし開始時間が定時開始より早ければ、(E7 - C7) * 24 でその差を計算し、プラスの始業前残業時間を表示します。
逆に、開始時間が遅ければ -(C7 - E7) * 24 でその時間差をマイナスとして計算し、表示します。

終業後残業の数式（H列）

=IF(OR(ISBLANK(F7), ISBLANK(D7)), 0, IF(F7 < D7, (D7 - F7) * 24, -(F7 - D7) * 24) - I7)

この数式は、終了時間（F列）と定時終了時間（D列）を比較して、終業後の残業時間を自動計算します。
今回は、終了時間または定時終了時間が未入力の場合も考慮し、空白なら「0」を表示するように設定しています。

数式の説明

IF(OR(ISBLANK(F7), ISBLANK(D7)), 0, ...):

OR(ISBLANK(F7), ISBLANK(D7)) では、終了時間（F列）または定時終了時間（D列）が空白の場合に、TRUE を返します。
この場合、数式は「0」を返し、残業時間が計算されません。
両方の時間が入力されている場合にのみ、次の計算式に進みます。

IF(F7 < D7, (D7 - F7) * 24, -(F7 - D7) * 24):

F7 < D7 では、「終了時間が定時終了時間より遅いかどうか」を確認します。
終了時間が定時より遅い場合は、(D7 - F7) * 24 で、その差を計算します。この結果、残業時間が正の数として表示されます。
終了時間が定時より早い場合は、-(F7 - D7) * 24 で、その差を計算し、早退した時間がマイナスの数値として表示されます。

- I16:

ここで、計算された深夜労働時間（I列の値）を終業後残業から差し引きます。これにより、深夜労働時間と残業時間が重複してカウントされるのを防ぎます。

深夜労働時間の数式（I列）

=IF(OR(ISBLANK(C7), ISBLANK(D7)), 0, IF(AND(C7<5/24, D7>22/24), ((5/24 - C7) + (D7 - 22/24))*24, IF(C7<5/24, (5/24 - C7)*24, IF(D7>22/24, (D7 - 22/24)*24, 0))))

この数式は、開始時間（C列）と終了時間（D列）を基に、深夜労働時間（22:00～翌5:00）の範囲内で働いた時間を自動計算します。
終了時間または開始時間が未入力の場合は、自動的に「0」として表示されます。

数式の説明

IF(OR(ISBLANK(C7), ISBLANK(D7)), 0, ...):

OR(ISBLANK(C7), ISBLANK(D7)) では、**開始時間（C7）または終了時間（D7）**が空白かどうかを確認します。
どちらかが空白の場合は、数式は「0」を返し、計算は行われません。

IF(AND(C7<5/24, D7>22/24), ((5/24 - C7) + (D7 - 22/24))*24, ...):

AND(C7<5/24, D7>22/24) は、「開始時間が翌日5:00より前、かつ終了時間が22:00より後であるか」を確認します。
該当する場合は、**深夜労働時間帯（22:00～翌5:00）**の両方にまたがる労働時間が計算されます。
(5/24 - C7) では、翌日5:00までに勤務した時間を計算し、(D7 - 22/24) では当日22:00以降の勤務時間を計算します。これらを合算し、合計深夜労働時間を求めます。

IF(C7<5/24, (5/24 - C7)*24, ...):

この部分では、開始時間が翌日5:00より前の場合、5:00までの深夜労働時間を計算します。(5/24 - C7) でその差を求め、深夜労働時間として計上します。
IF(D7>22/24, (D7 - 22/24)*24, 0):
この部分では、終了時間が当日22:00より後の場合、22:00以降の深夜労働時間を計算します。(D7 - 22/24) でその差を求め、深夜労働時間として計算されます。
それ以外の場合は「0」を返します。

休日労働時間の数式（J列）

=IF(OR(ISBLANK(C7), ISBLANK(D7)), 0, IF(OR(TEXT(A7, "ddd")="土", TEXT(A7, "ddd")="日", B7<>""), (D7 - C7)*24, 0))

この数式は、開始時間（C列）と終了時間（D列）が入力されている場合に、休日や祝日での労働時間を自動計算します。
土日や祝日に働いた場合に、労働時間が休日労働時間としてカウントされます。

数式の説明

IF(OR(ISBLANK(C7), ISBLANK(D7)), 0, ...):

開始時間（C列）または終了時間（D列）が空白かどうかを確認します。
どちらかが入力されていない場合は、労働時間を「0」とします。
両方が入力されている場合、次の計算に進みます。

IF(OR(TEXT(A7, "ddd")="土", TEXT(A7, "ddd")="日", B7<>""), (D7 - C7)*24, 0):

TEXT(A7, "ddd")="土" と TEXT(A7, "ddd")="日" は、日付（A列）を曜日に変換し、その日が土曜日または日曜日かどうかを確認します。
B7<>"" は、B列に祝日名が入力されているかどうかを確認し、祝日であればその行を休日とみなします。
土曜日、日曜日、またはB列に祝日名が入力されている場合、労働時間が休日労働として計算され、(D7 - C7) * 24 で労働時間の差を時間単位で計算します。
それ以外の日（平日など）であれば、この式は「0」を返し、休日労働時間としてカウントしません。

残業時間計の数式

=SUM(G7:H7)

始業前残業（G列）と終業後残業（H列）の合計を計算します。
これにより、1日あたりの総残業時間が求められます。

数式の説明

G7:H7 は、G列（始業前残業）からH列（終業後残業）までの範囲を指定しています。
SUM 関数は、この範囲に含まれる残業時間を合算し、その日の総残業時間を計算します。

G7～I7までの数式入力が完了したら、月末の行まで数式をコピーします。

表示形式（時間・数値）の設定

列	項目	表示形式
A	日付	日付形式
C～F	開始時間、終了時間、定時開始、定時終了	時間形式（h”:”mm）
G～J	始業前残業、終業後残業、深夜労働時間、休日労働時間	数値形式
L	残業時間合計	数値形式

先ほどの数式を入力しただけでは、表示形式が合わず勤務時間、残業時間が正しく計算されない状態です。

そのため、列によって表示形式を使い分けます。

A列: 日付

表示形式: 日付形式
理由: 日付の入力が必要なため、標準的な日付形式を使用します。
設定手順: 該当セルを選択 → 「表示形式」タブ → 「数字」→「カスタム日時」→ 月（8）/ 日 (5) (日 (火）) に設定。

C列～F列: 開始時間、終了時間、定時開始、定時終了

表示形式: 時間形式
理由: これらの列は勤務の開始・終了時間を表すため、時間形式h":"mmを使用します。

設定手順: 該当セルを選択 → 「表示形式」タブ → 「数字」→「カスタム日時」→ h":"mm に設定。

C列～F列: 開始時間、終了時間、定時開始、定時終了、休日労働時間

表示形式: 数値形式
理由: 残業時間や労働時間は、時間の差を計算した結果なので、数値形式で表示します。たとえば、1時間30分の残業は「1.5」として表示されるようにします。これにより、合計時間を正しく計算できます。
設定手順: 該当する列を選択 → 「表示形式」タブ → 「数字」→「数値」に設定。

L列: 残業時間合計

表示形式: 数値形式
理由: 全ての残業時間の合計が入る列のため、数値形式で設定します。
設定手順: 該当する列を選択 → 「表示形式」タブ → 「数字」→「数値」に設定。

スプレッドシートで勤怠管理表を作成するメリット・デメリット

メリット	デメリット
無料で使用できる自由にカスタマイズ可能他のGoogleツールと連携できる	手動での設定が必要複雑な数式や設定に慣れが必要誤入力によるミスが発生しやすい