勤務時間・残業時間などを計算する簡易な勤務表
１．背景
出退勤を管理する方法としては、細長い紙のタイムカードを機械に差し込み打刻する方法とか、磁気カードをかざすと出退勤時刻が記録されるシステムとかがあります。また、紙の表に手書きで記入しているところも多くあります。今回紹介する勤務表は、紙の表をExcelへの置き換えであったり、Excelは使っているが計算は手計算のまま というような方の一助になればと思い作成しました。
２．勤務表の概要
今回は勤務体系を以下の様に仮決めしました。・日をまたぐ勤務は無い。
・勤務単位は0時からの30分単位（9時とか9時半とか）とする。
・昼食時間は12:00～13:00とし、その１時間は勤務時間に含めず。（定数値としてシート上に記入。30分単位で変更可）
・17:00以降の勤務は「残業時間」とする。（定数値としてシート上に記入。30分単位で変更可）
・19:00以降の残業は「深夜残業」とする。（定数値としてシート上に記入。30分単位で変更可）
今回作成した勤務表の外観は図２－１のようになります。
図２－１
黄色背景の列（C列、D列、O列）はユーザー（従業者または管理者）が入力する部分で、C列には出勤時刻、D列には退出時刻、O列には備考が入ります。出勤した場合にはC列・D列は必須入力となります。
出勤時刻・退出時刻を入力すると、その日の30分単位の勤務時間（L列）、残業時間（M列）、深夜残業時間（N列）が表示されます。
また勤務内容（I～K列）として、「休日の勤務か」「午前中働いたか、午後働いたか」が〇印で分かるようにしています。このデータと、曜日のデータを合わせれば「休日出勤」「半休」「年休」などの情報を得ることが可能です。
なお、日付（A列）と曜日（B列）は、A7セルの「月の初日」に結びつけていますので、A7セル値を変更すれば勤務表の日付が入れ替わります。また、今回はマクロは一切使用せず、ワークシート関数のみで作っています。
３．ワークシート上の数式など
図２－１では、内部計算の部分は非表示にしてあります。それを展開（E～H列、1～6行目）したのが図３－１になります。３ー１．日付関係
日付関係はA列とB列です。基準となるのはA7セルの「月の初日」の日付データで、図３－１の場合は「2020/11/01」がA7セルに入力されています。実際の日付欄の先頭であるA10セルには「＝A7」の数式が入力されており、その下のA11セルには「＝A10+1」が入っています。A11セルをコピーし、月の一番下まで数式貼付け（または、A11セルの右下角を下方向にドラッグ）すれば、その月の日付が埋まってくれます。
図３－１
また、B列は曜日を入れています。その左隣の日付に対する曜日とするため、B10セルには数式「＝TEXT(A10,"aaa")」を入力します。
式の中で指定している「"aaa"」は「曜日を簡易型（「日」など）で表示」するものです。詳細な表示（「日曜日」など）をする場合は「"aaaa"」として下さい。
曜日列を作ると、今度は休日（今回は土曜・日曜を休日としています）であることを目立つようにしたくなります。
その目立たせる一般的な方法が、セルに背景色を付ける方法です。今回は図３－２のように「条件付き書式」を使って背景色を設定しています。
図３－２
条件付き書式では、Excelのバージョンが新しいものであれば色々な設定が出来ますが、基本は「数式を使用して書式設定」するものです。図３－２では、数式が「=OR($B10="土", $B10="日")」、書式が「背景を薄い赤色」、その条件付き書式を適用する範囲が「=$A$10:$O$40」となります。
これを今回のワークシートに照らし合わせると図３－３の様になります。
図３－３
数式の中で指し示しているセルは「$B10」という位置で、この値が「土」または「日」であれば「背景を薄い赤色」にします。
またその書式を適用する範囲は、勤務表の範囲である「=$A$10:$O$40」です。
この時のセル「$B10」の意味ですが、列Bの前に「$」マークがついています。これは「B列で条件に合致するセルがあれば、そのセルの行全体を指定の書式にする」という意味になります。「$」マークをどこに付けるかによって書式の適用が変わりますが、それをまとめたのが図３－４になります。
図３－４
図３－４はB2～G10セル範囲（黒枠内）に対し、数式「＝B2＞５」の条件で書式（ここでは、セル背景をオレンジ色）を与えたものです。「B2」の行側・列側に対して「$」マークの付け方を様々変えてみました。尚、黒枠内には0～9の数字をランダムに配置しています。
①：まず行側・列側の両方に「$」マークを付けた「$B$2」は、行・列の両方とも固定しているため、「B2セル」の値だけが条件の対象となります。図３－４の場合は、B2セルの値は「８」ですので「$B$2＞５」が成立します。ですので適用先のセル範囲の全てが、オレンジ色の背景色となります。
②：列側のみに「$」マークを付けた「$B2」は、列側のみを固定しているため、B列にある各セルの値が条件の対象になります。ですので、B列かつ適用範囲内のセル値が「５」以上のセルは条件が成立することになります。
そして、その条件が成立しているセルのある「行全体（但し適用範囲内）」が、オレンジ色の背景色となります。
③：行側のみに「$」マークを付けた「B$2」は、行側のみを固定しているため、2行目にある各セルの値が条件の対象になります。ですので、2行目かつ適用範囲内のセル値が「５」以上のセルは条件が成立することになります。
そして、その条件が成立しているセルのある「列全体（但し適用範囲内）」が、オレンジ色の背景色となります。
④：行側・列側のどちらにも「$」マークが無い「B2」は、行・列の両方とも固定していないため、適用範囲内の全てのセルが条件の対象となります。ですので対象範囲内のセルの内、条件の「５を超える」値のあるセルがオレンジ色の背景色となります。
今回の条件付き書式は、図３－４の内②に相当しますので、「B列で、土または日が入っている行」の背景色が薄い赤色になります。
なお、図３－１でE～H列は手動で背景色を青くしているのですが、例えばE10～H10のセル範囲は薄い赤色になっています。これは「元の書式設定（背景色設定）の上に条件付き書式が上書き」されるためです。
３ー２．出退勤時刻の計算
C～D列には、実際に出退勤した時刻を入力します。手動での入力の他、カードリーダー等からのマクロ入力も考えられます。なお、時刻には通常日付のデータも含まれています。例えば「2020年11月1日の8:30」は「44136.3541666667」という数値になります。この値の整数部分（44136）が年月日を表し、小数点以下（0.3541666667）が時分秒を表しています。
今回は、出退勤時刻（C～D列）は「日付を含めない時刻」として計算式を立てています。、数値で言えば「0～1未満」の値（言ってみれば1900年1月1日の時刻）を入力しています。もし日付も入る値を入力する場合には、E列以降の数式内の出勤時刻に当たる「C10」という部分は「C10-INT(C10)」に、退出時刻に当たる「D10」は「D10-INT(D10)」に置き換えて下さい。
図３－５
まず計算に使用する設定値をF2～F5セルに配置してあります。上の方から「昼休み開始時刻」「昼休み終了時刻」「残業開始時刻」「深夜残業開始時刻」です。様々な勤務体系があるのは分かっていますが、今回は単純な体系で数式を組み立てています。
また今回は「勤務は30分単位」と仮定しましたので、例えば8:25に出勤した場合は8:30からの勤務開始、8:35に出勤した場合は9:00からの勤務開始となります。
また勤務終了時刻も、17:10まで勤務した場合は17:00までの勤務、16:59まで勤務しても16:30までの勤務となります。
３ー２ー１．規定出勤時刻の計算（E10セル）
E10セルの「30分単位での規定出勤時刻」の数式は「=INT(-1*(C10*24)*2)/-2」となっています。C10の出勤時刻は、表示上は時分(秒)ですが、数値としては「0～1未満」です。つまり「１日24時間を１」とする値ですので、時刻を０時～２４時という数値にするには「24を掛ける」必要があります。これが「(C10*24)」の部分になります。
その上で「規定の勤務開始時刻（30分単位）」にするために、30分単位での切り上げをします。
時刻を30分単位に切り上げるには、24時間単位の時刻「(C10*24)」に対し「0.5時間（＝30分）で割った後、整数部分で切り上げし、また0.5時間（＝30分）を掛ける」という作業をします。
切り上げというと、まず「ROUNDUP」関数を思い浮かべると思いますが、今回はINT関数を使って切り上げをします。
図３－６
図３－６は数式「=INT(-1*(C10*24)*2)/-2」の前半部分「INT(-1*(C10*24)*2)」を説明している図です。
まず「C10*24」は24時間単位での出勤時刻を表し、30分単位で切り上げ処理をするため0.5時間（＝30分）で割ります（＝２を掛ける）。その値に対して「-1」を掛けてマイナス値にし、INT関数を使用して実質上の切り上げをしています。
INTは切り捨てのイメージがあると思いますが、INTは「元の数値より小さい整数を取得する」関数です。正の数値に対してINT関数を使えば確かに切り捨てになりますが、マイナス値にしてからINT関数を使えば「マイナス側での切り上げ」になります。
その「マイナス側での切り上げ」値に対してもう一度マイナスを掛けて正の値に戻し、かつ0.5時間（＝30分）を掛ける（＝２で割る）ことで、例えば8:15を8:30に「30分単位で切り上げる」ことが出来ます。
なお、INT関数の代わりに「ROUNDUP関数」を使うのであれば、数式は「=ROUNDUP((C10*24)*2,0)/2」となります。
このような切り上げ・切り捨て等については「様々な数値の丸め方」も参考にして下さい。
３ー２ー２．規定退出時刻の計算（F10セル）
F10セルの「30分単位での規定退出時刻」の数式は「=INT(D10*24*2)/2」です。規定出勤時刻と違い、規定退出時刻は純粋に30分単位での切り捨てを行えば良いので、マイナス値にするなどの細工は必要ありません。まず「D10*24」は24時間単位での退出時刻を表し、30分単位で切り捨て処理をするため0.5時間（＝30分）で割ります（＝２を掛ける）。
その値（＝正の値）に対してINT関数を使用することで切り捨てを行い、最後に0.5時間（＝30分）を掛ける（＝２で割る）ことで、例えば17:15を17:00に「30分単位で切り捨てる」ことが出来ます。
なお、INT関数の代わりに「ROUNDDOWN関数」を使うのであれば、数式は「=ROUNDDOWN((C10*24)*2,0)/2」となります。
３ー２ー３．AM勤務時間の計算（G10セル）
まず、出勤時刻と退出時刻の組み合わせは、図３－７のように７パターンあります。出勤時刻が各線の●印位置、退出時刻が矢印位置です。
図３－７
午前中働いているパターンは①～③なので、その３つについて「午前中の勤務時間」を式で表してみます。
| パターン | 午前中の勤務時間の式 |
|---|---|
| ① | 退出時刻 ー 出勤時刻 |
| ② | 昼休み開始時刻 ー 出勤時刻 |
| ③ | 昼休み開始時刻 ー 出勤時刻 |
この３つを一つの数式で表すのですが、IF文を使う前に、ここでは以下のように「午前中勤務の終わりの時刻」に注目します。
①は、退出時刻＜昼休み開始時刻
②と③は、退出時刻＞昼休み開始時刻
つまり「午前中勤務の終わりの時刻」は「退出時刻 か 昼休み開始時刻 の小さい方」になります。数式っぽくすると「MIN（昼休み開始時刻,退出時刻）」です。
この「午前中勤務の終わりの時刻」から「出勤時刻」を引けば「午前中の勤務時間」になりますので、数式としては「MIN（昼休み開始時刻,退出時刻）－出勤時刻」→Excelの数式にすると「MIN($F$2*24,F10)-E10」となります。
残りの④～⑦は「午前中は仕事をしていない」ので「午前中の勤務時間＝０」です。これを式で表現するにはいくつかの方法があると思いますが、少なくとも④～⑥と⑦は分けて処理する必要がありそうです。
④～⑥をゼロとする式は、１つはIFを使い「IF(出勤時刻＞昼休み開始時刻,0,MIN($F$2*24,F10)-E10)」とする方法。もう１つは先程の数式「MIN($F$2*24,F10)-E10」を④～⑥に当てはめると「マイナス値」となることから、マイナスの時にゼロとなるように「=MAX(0,MIN($F$2*24,F10)-E10)」とする方法です。
また⑦をゼロとする式は「IF(出勤時刻＝0,0,････」という方法が簡単だと思います。
ということで、今回の数式は「=IF(E10=0,0,MAX(0,MIN($F$2*24,F10)-E10))」としました。もちろん「IF(出勤時刻＞昼休み開始時刻,0,････」を使っても構いませんし、考えつかなかったもっと良い式があるかもしれませんが、今のところ今回の式が最も短いと思われます。
３ー２ー４．PM勤務時間の計算（H10セル）
午後に働いているパターンは③、⑤～⑥なので、その３つについて「午後の勤務時間」を式で表してみます。| パターン | 午前中の勤務時間の式 |
|---|---|
| ③ | 退出時刻 ー 昼休み終了時刻 |
| ⑤ | 退出時刻 ー 昼休み終了時刻 |
| ⑥ | 退出時刻 ー 出勤時刻 |
この３つを一つの数式で表すのですが、IF文を使う前に、ここでは以下のように「午後勤務の始まりの時刻」に注目します。
③と⑤は、出勤時刻＜昼休み終了時刻
⑥は、出勤時刻＞昼休み終了時刻
つまり「午後勤務の始まりの時刻」は「出勤時刻 か 昼休み終了時刻 の大きい方」になります。数式っぽくすると「MAX（昼休み終了時刻,出勤時刻）」です。
「退出時刻」から、この「午後勤務の始まりの時刻」を引けば「午後の勤務時間」になりますので、数式としては「退出時刻－MAX（昼休み終了時刻,出勤時刻）」→Excelの数式にすると「F10-MAX($F$3*24,E10)」となります。
残りの①②、④、⑦は「午後は仕事をしていない」ので「午後の勤務時間＝０」です。
まず①②・④をゼロとする式は、IFを使う場合は「IF(退出時刻＜昼休み終了時刻,0,F10-MAX($F$3*24,E10)」となります。また先程の数式「F10-MAX($F$3*24,E10)」を①②・④に当てはめると「マイナス値」となることから「=MAX(F10-MAX($F$3*24,E10),0)」とする方法も可能です。
また⑦をゼロとする式は「IF(出勤時刻＝0,0,････」でも良いのですが、「F10-MAX($F$3*24,E10)」に「出勤しない時の値」を当てはめてみると「0 - MAX(13,0)」（昼休み終了時刻を13:00とした場合）と、①②・④と同様に「マイナス値」となります。
ですので、⑦のパターンも含めて「=MAX(F10-MAX($F$3*24,E10),0)」で良いことが分かります。
なお「忘れ物を取りに来ただけで仕事はしていない」みたいな場合があります。例えば、次のような出退勤時刻を考えてみます。
①-1：出勤時刻＝10:05／退出時刻＝10:20（実勤務時間＝15分間）
①-2：出勤時刻＝10:05／退出時刻＝10:55（実勤務時間＝50分間）
①-1の場合には、E列・F列の計算では「規定出勤時刻=10:30 ＞ 規定退出時刻=10:00」となります。また①-2の場合は「規定出勤時刻=10:30 ＝ 規定退出時刻=10:30」となり、この条件をG列・H列の数式に入れてみると両方とも「ゼロ」となります。
理由が「忘れ物」であれば良いのですが、「１時間弱も会社に居たのに勤務時間はゼロ」と計算されることは社員に充分周知しておいた方が良いと思います。
３ー３．勤務内容の計算
E～H列での計算結果を元に、半休などの状況や勤務時間、残業時間の計算をI～N列で行います。数式は図３－１０となります。なおI～K列の勤務内容の部分は「休出」「AM半休」「PM半休」「年休」などの文字列を表示させる方法も考えましたが、会社独自の休みや出勤、長期連休などにも全て対応させるのは不可能と判断し、最低限の図３－１０の３項目表示としました。
図３－１０
３ー３ー１．休日出勤（休出）の計算（I10セル）
休出したか否かの条件は「勤務時間がゼロで無いこと」＋「その日が休日であること」になります。「勤務時間がゼロで無い」を式で表すと「G10+H10>0」、「その日が休日」はB列の値を使って「OR(B10="土",B10="日")」とするのが簡単と思います。この２つの条件を両方とも満たしていることが必要ですので、ANDでつないで「AND(G10+H10>0,OR(B10="土",B10="日"))」という条件式になります。
あとは、I10セルに「休出の時には〇印」とするのでしたら「=IF(AND(G10+H10>0,OR(B10="土",B10="日")) ,"〇","")」
という数式になります。
３ー３ー２．半日出勤（半出）の計算（J10・K10セル）
G列・H列で「午前・午後の勤務時間」を計算していますので、その値を調べ（午前・午後の勤務時間＞０）て「〇印」を付けています。実際には、午前中勤務は「=IF(G10>0,"〇","")」、午後勤務は「=IF(H10>0,"〇","")」という数式になります。なお、午前にも午後にも〇印がついていれば、「１日仕事をした」ことになります。
３ー３ー３．勤務時間・残業時間・深夜残業時間の計算（L10セル、M10セル、N10セル）
「勤務時間」「残業時間」「深夜残業時間」の定義は会社によって異なると思いますので、今回の定義を図３－１１に示します。
図３－１１
「勤務時間」は、規定出勤時刻から規定退出時刻までの時間（昼休みが掛かっている場合は、その時間は除く）としました。
「残業時間」は、あらかじめ定めた残業開始時刻以降に仕事をした時間（規定退出時刻まで）としました。
「深夜残業時間」は、あらかじめ定めた深夜残業開始時刻以降に仕事をした時間（規定退出時刻まで）としました。
もし定義が異なる場合は、数式を調整して下さい。
まずL列の「勤務時間」は、G列・H列で計算した午前勤務時間と午後勤務時間を足した値になりますので、数式としては「=G10+H10 」となります。
M列・N列の「残業時間」「深夜残業時間」は、図３－１２のように「何時に出勤したか」を考慮する必要があります。
単純に「退出時刻 ー 残業開始時刻」の様な式を作ってしまうと、「22:00に来て、23:00に帰った」人は「残業6時間、深夜残業4時間」などと計算されてしまいます。
図３－１２
まず「残業時間」についてですが、図３－１２の②～③の場合は「残業開始時刻よりも前から仕事をしている」ので、残業時間を表す式は「退出時刻 ー 残業開始時刻」で問題ありません。しかし④～⑥の場合は「残業開始時刻よりも後に出勤している」ので、残業時間は「退出時刻 ー 出勤時刻」になります。
ですので、「残業開始時刻」と「出勤時刻」を比較して大きい方を採用すれば良いことになりますので、数式としては「F10-MAX(E10,$F$4*24)」となります。
次に残業開始時刻まで仕事をしていない①の場合について、先程の式「F10-MAX(E10,$F$4*24)」で計算すると「マイナス値」となりますので、MAX関数を使って「=MAX(F10-MAX(E10,$F$4*24),0)」とすれば、「残業開始時刻まで仕事をしていない人は、残業時間ゼロ」にすることが可能です。（もちろん、IF文で分岐させることも出来ます）
また、⑦の「出勤せず」の場合も、残業開始時刻まで仕事をしなかった場合と同じく「マイナス値」となるため、この式で問題ないことが分かります。
「深夜残業時間」についても「残業時間」と同様に、「残業開始時刻」と「出勤時刻」の比較、及び「マイナス値はゼロ」にする式を組み立てれば良いことになります。よって数式は「=MAX(F10-MAX(E10,$F$5*24),0)」となります。
以上10行目で数式を組み立てた後、１か月分を数式貼り付けます。また内部計算部分（E～H列、1～6行目）は非表示にします。
なお、１か月の日数は月によって変わりますが、今回特に「４月の場合は30日分にする」のようなマクロは作っていませんので、手動で不要部分を非表示にして下さい。
また、貼り付けた数式をユーザーに書き換えられないように、実際に使用する際には「シート保護」などの処置が必要と思います。
４．最後に
今回紹介した勤務表は、非常にシンプルな勤務体系と仮定して数式を組み立てました。実際には非常に複雑な会社が多いと思いますので、とてもサンプルファイルそのままでは使い物にならないと思っています。ただし複雑になればなるほど、図３－７や図３－１２のように全ての場合を書き出してから数式化することが重要と思いますし、また勤務表は直接賃金に結び付きますのでミスは許されません。様々な値を入力してみて本当に正しい結果が得られているかの充分な検証も必要と思います。
勤務時間・残業時間などを計算する簡易な勤務表（it-046.xlsx）