休憩を差し引く勤務時間計算
１．背景
最近では在宅勤務も増えていて、どのように勤務時間管理しているのか興味がありますが、会社に行って仕事をする場合には、一般的に「出社・退社の時刻」を元に勤務時間・残業時間などを計算します。その計算方法については各社各様の方法があるので、今回は一例として「日を跨ぐ勤務」「社で決めた一斉休憩時間を勤務時間から差し引く」の計算式を主に紹介します。
なお「勤務時間・残業時間などを計算する簡易な勤務表」では、休日出勤・半休・残業時間・深夜残業などの計算を紹介していますので参考にして下さい。
２．システム概要
今回の勤務体系は以下の様に仮決めしました。・日を跨ぐ勤務が可能。
・在社時間は、２４時間以下。
・勤務単位は３０分単位。
・１日の中で、２回出社することは無い。
以上の条件の元で、図２－１のように出社・退社したとします。なお、薄オレンジ色で塗りつぶした時間帯は「全社一斉休憩時間」であり、青線の前後の丸印が「出社時刻」「退社時刻」を表します。
図２－１
この「出社時刻」「退社時刻」を何らかの方法でExcelに記入したのが、図２－２です。入力セルはC列・D列になります。
（「何らかの方法」とは、Excelマクロでの時刻入力や、タイムカードを読み取ってExcelに手入力する 等が考えられます。）
図２－２
「出社時刻」「退社時刻」は、規定の30分単位キッチリになるとは限りません。例えば２日の出社時刻は「20:10」ですが、「勤務単位は30分単位」ですので「20:30からの勤務」という事になります。また退社時刻も同様に、例えば３日は「17:30まで働いた」後「17:40に会社を出た」ことを意味しています。
また日を跨ぐ勤務の場合、退社時刻は２日の様に「1:35」と翌日の24時間時刻で入力することも、４日のように「28:35」と24時間を超えた時刻で入力することも可能です。「在社時間は24時間未満」ですので、「出社時刻>退社時刻」であれば「日を跨いだ勤務」と判断して計算します。
但し「全社一斉休憩時間（図２－１で薄オレンジ色で塗りつぶした時間帯）」の間は、例え出社していても「勤務時間外」です。
以上を考慮して計算すると、図２－２の「在社時間（Ｉ列）」「勤務時間（ＡＨ列）」の計算結果になります。これを図２－１のチャート上で確認したのが図２－３になります。
図２－３
例えば３日の日中の勤務は「7:15から17:40まで在社」しています。30分単位ですので会社時間では「7:30から17:30まで在社（10時間）」ということになります。その中で7:00～8:00、12:00～13:00、17:00～18:00は休憩時間ですので、「7:30～8:00（0.5時間）」「12:00～13:00（1時間）」「17:00～17:30（0.5時間）」は休憩中（合計2時間）となりますので、勤務時間は「10.0 － 2.0 ＝ 8.0」時間という事になります。
なお、今回の「全社一斉休憩時間」を、「在社時間の中で、基準時間帯（例 7:30～17:30）は何時間か」などを求める計算式に置き換えることも可能です。
また、勤務表の年月を切り替えるには「A6セルの日付値を勤務表の年月の1日付けの日付」に書き換えて下さい。書き換えることで、図２－２の日付（A列）と曜日（B列）が、指定した年月のものに切り替わります。また「月末の処理」で説明しますが、日数の少ない月の月末（＝翌月分の行）は、黒で塗りつぶして入力できないようにしています。
３．計算式の概要
お気付きの通り、図２－２は行・列を一部非表示にしています。再表示させると、図３－１のようになります。背景色が付いていない部分が基本的に非表示部分（1～5行目、E～H列、J～AG列）です。
図３－１
C列・D列（図３－１の①）は、ユーザーが入力する「出社時刻」「退社時刻」です。入力値は「日付を含まない時刻」で、VBAで言えばSingle型になります。
縦方向に日付が並んでいますので、まず出社した日の行の「出社時刻セル（今回のC列）」に時刻を入力します。そして「退社時刻」を「出社した日付の行の退社時刻セル（今回のD列）」に入力します。退社時刻が翌日になる場合は、翌日の時刻（例 1:35）を入力してもOKですし、出社日の24時超の時刻（例 25:35）でもOKです。
E列・F列（図３－１の②）は、ユーザーが入力した「退社時刻」が翌日の場合に「出社日の24時超の時刻」に修正しています。その計算方法は、C列とD列の値を比べ「出社時刻>退社時刻」であれば「退社時刻は翌日の時刻」と判断をしています。逆に「出社時刻<退社時刻」であれば、退社時刻は「出社した日と同日」または「翌日になった退社時刻を24時間超の値として入力している」と判断します。
なおE列の値（出社時刻）は、C列の値（出社時刻）と全く同じため、イコールで単純にコピーをしています。
G列・H列（図３－１の③）は、②で修正した出社・退社時刻を「会社の勤務単位時間に修正」しています。今回は勤務単位を「30分単位」としましたので、例えば３日のように「出社時刻＝7:15」だった場合は「7:30」に、「退社時刻＝17:40」だった場合は「17:30」に修正します。「給与を支払う単位時間」と言っても良いかもしれません。
但し１日のように「出社時刻＝6:00」の場合も「6:00」と計算され、「出社したばかりで働く準備が出来ていないのに、給与を払う」計算になっていますが、「拘束時間」と考えれば納得できるかもしれません。
I列（図３－１の④）は、③で計算した「勤務単位時間に修正した、出社・退社時刻」の差を計算した「在社時間」です。
J～AG列（図３－１の⑤）は、在社時間④と休憩時間とが重複している時間を計算します。
計算式は１ヶ所の休憩時間帯に対して3列を使っており、今回は８ヶ所の休憩時間に対応させています。図３－１では休憩時間を設定する枠はC2～D5セルの４ヶ所ですが、24時間超の時刻も含めて計算させるため、2日分の8ヶ所が必要になります。
休憩開始・完了時刻は「休憩時間の設定枠（C2～D5セル）」の値をコピーして使用しています。例えば２行目（C2セル・D2セル）の休憩時間（0:00～1:00）の休憩開始・終了時刻は、J7セル・K7セルにコピーしています。また、24時間超の時刻の場合は翌日の0:00～1:00となりますので「J7セル・K7セルの値に１（＝翌日）を加えて、V7セル・W7セルの値とする」ようにしています。
計算には１休憩時間当たり3列（例えばJ～L列）を使っており、休憩開始完了時刻は7行目を参照しているため、休憩時間計算の8か所の数式は相対的に同じ式になっています。
休憩時間計算の3列の内、１列目は「在社時間の中に『休憩開始時刻』が含まれているか」、２列目は「在社時間の中に『休憩完了時刻』が含まれているか」を計算し、含まれていればTrueを、含まれていなければFalseとします。
そのTrue・Falseの判断値を使って、３列目で「在社時間と休憩時間が重複している時間」を計算します。
もちろん３列も使わずに１列のみでも数式は組み立てられます。それどころか複数個の休憩時間の総休憩時間も１つの数式で表すこともできるはずです。しかし数式が複雑になり、分かり難く、また修正しにくくなってしまうために、今回は３列×８ヶ所に分けて計算させています。
最後にAH列（図３－１の⑥）で、在社時間④から休憩時間⑤を差し引いて、勤務時間⑥としています。
４．計算式詳細
４－１．退社時刻修正
F列は、ユーザーが入力した「退社時刻」が翌日の場合に「出社日の24時超の時刻」に修正しています。なおE列は、ユーザー入力の「出社時刻」と同一値です。
図４－１
数式は８行目を例にすれば、図４－１のように「=IF(D8="",0,IF(D8>C8,D8,D8+1))」としました。
メインは数式後半の「IF(D8>C8,D8,D8+1)」で、「出社時刻（C8）と退社時刻（D8）が逆転」している場合は「退社時刻は、翌日の時刻」と判断し、「退社時刻（D8）に１を加える」ことで24時間超の時刻に修正をしています。
なお24時間超の値で退社時刻を入力している場合は、「出社時刻と退社時刻が逆転しない」ので、そのままとなります。
数式前半の「=IF(D8="",0,･･･」は、「退社時刻が入力されていない場合」はゼロとしています。この前半部分が無いと、例えば「6:00に出社し、退社時刻は空欄」の場合でも「6:00から翌日の0:00まで働いた」と計算されます。更に「出社・退社とも空欄（例えば休日）」の場合でも「0:00から翌日の0:00まで働いた」ことになってしまいます。
この誤計算を防ぐために、数式前半が必要です。
但しこの数式では、出社時刻に「0:00」を入力した場合に「何も入力していない」時と同じ計算結果（在社時間＝ゼロ）が表示されてしまいます。出社時刻だけを入力した場合「在社時間＝****･･･」と表示したい場合は、
「=IF(AND(D8="",C8<>""),-1,IF(D8>=C8,D8,D8+1))」
などのような数式とする必要があります。この場合、1:00の出社で翌日の1:00退社でも、在社時間＝ゼロと計算されてしまうことには注意が必要です（在社時間のシステム条件を「２４時間以下→２４時間未満」にする必要あり）。
４－２．時刻を勤務単位に修正し、在社時間を算出
G列・H列は、E列・F列で24時間超に修正した出社・退社時刻を「会社の勤務単位時間に修正」しています。
図４－２
今回「勤務時間は30分単位」としましたので、例えば7:15に出社し 17:40に退社した場合は、図４－３のように「7:30～17:30が勤務対象時間」となります。
図４－３
この勤務対象時間を求めるのが、以下の数式（図４－２のG8・H8セル）となります。
出社時：=INT(-1*E8*24*60/30)/(24*60/30)*-1
退社時：=INT(F8*24*60/30)/(24*60/30)
まず、分かり易い退社時の数式「=INT(F8*24*60/30)/(24*60/30)」で説明します。退社時刻である「F8セルの値」は「24時間で1」となる数値です。分単位で表せば「24 × 60 で 1」です。同様に30分単位で表せば「24 × 60 / 30 で 1」となります。
退社時刻を丸める（切り捨て）にはINTを使って小数点以下を切り捨てれば良いので「INT(F8*24*60/30)」とすることで、30分単位に丸められます。最後に「24時間で1」の単位に戻すために、(24*60/30)で割って「=INT(F8*24*60/30)/(24*60/30)」という数式になります。
出社時は、30分単位に「切り上げ」ます。数式としては「=INT(-1*E8*24*60/30)/(24*60/30)*-1」としました。
切り上げには「様々な数値の丸め方」でも紹介したように「ROUNDUP関数」などを使う方法がありますが、INT関数を使い図４－４のように「一旦マイナスにしておき、切り捨てる」ことで、結果的に「切り上げる」ことが可能です。
INT関数内の「-1」でマイナス側にしてからINTで切り捨て計算をし、数式最後の「*-1」で元に戻しています。
図４－４
なお「30分単位」では無く、「20分単位」「15分単位」の場合もあるかもしれません。その際は出社退社の計算式の「30」の部分を「20」「15」に置き換えればOKです。例えば15分単位で出勤を管理する場合は、以下の数式となります。
出社時：=INT(-1*E8*24*60/15)/(24*60/15)*-1
退社時：=INT(F8*24*60/15)/(24*60/15)
30分単位の勤務時間に修正した後、「規定退社時刻－規定出社時刻」を計算したのが、図４－５「在社時間」となります。出社時：=INT(-1*E8*24*60/15)/(24*60/15)*-1
退社時：=INT(F8*24*60/15)/(24*60/15)
図４－５
４－３．休憩時間の計算と、勤務時間を算出
今回の勤務時間は最長24時間ですので、１日の終わり（24時近く）が出社時刻の場合は翌日の24時くらいまでが勤務時間対象となります。つまり２日分（48時間）の休憩時間を考慮する必要があります。そして休憩時間は１日当たり４回としていますので、図４－６のように「4回/出社当日 ＋ 4回/出社翌日 ＝ 合計８回」の休憩時間を計算します。
図４－６
４－３－１．休憩時間帯データのコピー
休憩時間の計算は、３列で１つの「休憩時間帯」の計算をしています。図４－７で言えば、休憩時間のデータ元（C2～D5セル範囲）から７行目にデータをコピーし、３列の計算元データとしています。なお図４－７の６行目タイトルの内、休憩Sが「休憩開始時刻」を、休憩Eが「休憩終了時刻」を表しています。
また24時間超の休憩時間については、24時間以下の計算元データに「+1（24時間）」を足しています。
図４－７
４－３－２．在社時間と休憩時間の重なりチェック
３列で１つの憩時間帯の計算の内、１列目は「在社時間と休憩開始時刻の重なり有無」を調べ、２列目は「在社時間と休憩終了時刻の重なり有無」を調べています。
図４－８
図４－８の吹き出しのあるセルで説明します。数式は、休憩開始時刻を調べるM8セルでは「=AND($G8<=M$7,$H8>M$7)」、休憩終了時刻を調べるN8セルでは「=AND($G8<N$7,$H8>=N$7)」となります。各数式の結果は、TrueまたはFalseです。
各数式を図にしたのが図４－９です。左側が「在社時間と休憩開始時刻の重なり有無」、右側が「在社時間と休憩終了時刻の重なり有無」になります。
ある休憩時間を7:00～8:00の水色部分、その下の両端が丸の線が在社時間を表します。在社時間の線の左側の丸が出社時刻、右側の丸が退社時刻となります。休憩時間との重なり具合は、図４－９では１１種類について検討しています。
図４－９
図４－９の左側は、休憩開始時刻と在社時間を比較している「=AND($G8<=M$7,$H8>M$7)」を表す図です。数式のANDの中の１番目の「$G8<=M$7」は「出社時刻 <= 休憩開始時刻」です。赤い縦点線の休憩開始時刻よりも出社時刻（横線の左側の丸印）の方が早い（＝左側にある）場合がTrueとなります。
一方、ANDの中の２番目「$H8>M$7」は「退社時刻 > 休憩開始時刻」です。赤い縦点線の休憩開始時刻よりも退社時刻（横線の右側の丸印）の方が遅い（＝右側にある）場合がTrueとなります。
２つの式をANDでつなげていますので、両方ともTrueの時のみがTrueになります。図４－９で言えば、全体としてTrueとなる在社時間は、青色の線で示しています。
同様に図４－９の右側は、休憩終了時刻と在社時間を比較している「=AND($G8<N$7,$H8>=N$7)」を表しており、出社時刻側・退社時刻側ともTrueとなる在社時間は、青色の線となります。
この在社時間と、「休憩開始時刻」の重なり有無（True、False）及び「休憩終了時刻」の重なり有無を分類すると、図４－１０のように①②③＋それ以外の４種類に分けられます。
図４－１０
これを表にすると図４－１１のようになります。それぞれ「休憩時間の計算方法」のような計算方法を使えば、在社時間と休憩時間が重なっている時間が計算できます。
図４－８
図４－８の吹き出しのあるセルで説明します。数式は、休憩開始時刻を調べるM8セルでは「=AND($G8<=M$7,$H8>M$7)」、休憩終了時刻を調べるN8セルでは「=AND($G8<N$7,$H8>=N$7)」となります。各数式の結果は、TrueまたはFalseです。
各数式を図にしたのが図４－９です。左側が「在社時間と休憩開始時刻の重なり有無」、右側が「在社時間と休憩終了時刻の重なり有無」になります。
ある休憩時間を7:00～8:00の水色部分、その下の両端が丸の線が在社時間を表します。在社時間の線の左側の丸が出社時刻、右側の丸が退社時刻となります。休憩時間との重なり具合は、図４－９では１１種類について検討しています。
図４－９
図４－９の左側は、休憩開始時刻と在社時間を比較している「=AND($G8<=M$7,$H8>M$7)」を表す図です。数式のANDの中の１番目の「$G8<=M$7」は「出社時刻 <= 休憩開始時刻」です。赤い縦点線の休憩開始時刻よりも出社時刻（横線の左側の丸印）の方が早い（＝左側にある）場合がTrueとなります。
一方、ANDの中の２番目「$H8>M$7」は「退社時刻 > 休憩開始時刻」です。赤い縦点線の休憩開始時刻よりも退社時刻（横線の右側の丸印）の方が遅い（＝右側にある）場合がTrueとなります。
２つの式をANDでつなげていますので、両方ともTrueの時のみがTrueになります。図４－９で言えば、全体としてTrueとなる在社時間は、青色の線で示しています。
同様に図４－９の右側は、休憩終了時刻と在社時間を比較している「=AND($G8<N$7,$H8>=N$7)」を表しており、出社時刻側・退社時刻側ともTrueとなる在社時間は、青色の線となります。
この在社時間と、「休憩開始時刻」の重なり有無（True、False）及び「休憩終了時刻」の重なり有無を分類すると、図４－１０のように①②③＋それ以外の４種類に分けられます。
図４－１０
これを表にすると図４－１１のようになります。それぞれ「休憩時間の計算方法」のような計算方法を使えば、在社時間と休憩時間が重なっている時間が計算できます。
| 番号 | 休憩開始側 | 休憩終了側 | 休憩時間の計算方法 |
|---|---|---|---|
| ① | True | False | 休憩開始時刻～退社時刻 |
| ② | True | True | 休憩時間の全部 |
| ③ | False | True | 出社時刻～休憩終了時刻 |
| それ以外 | False | False | なし |
４－３－３．在社時間に含まれる休憩時間の計算
この図４－１１を数式にしたのが、図４－１２です。数式としては「=IF(AND(M8,N8),N$7-M$7,IF(M8,$H8-M$7,IF(N8,N$7-$G8,0)))」となります。
図４－１２
数式は図４－１３のように、図４－１１の①②③及びそれ以外に分けて考えます。
図４－１３
まず②の部分は「休憩開始側（M8）＝True」且つ「休憩終了側（N8）＝True」の場合です。
この場合は、在社時間内に完全に休憩時間が含まれていますので、「全ての休憩時間」が対象となります。ですので「休憩終了時刻（N$7） － 休憩開始時刻（M$7）」が休憩時間となります。
次に①の部分は「休憩開始側（M8）＝True」の場合です。②より後ろ側にありますので「休憩終了側＝False」です。
この場合は「在社時間内に休憩開始時刻は存在する」が、「休憩時間内に退社」してしまうために「休憩終了側＝False」となっている事になります。ですので「退社時刻（$H8） － 休憩開始時刻（M$7）」が休憩時間となります。
③の部分は「休憩終了側（N8）＝True」の場合です。②①より後ろの部分ですので「休憩開始側＝False」です。
この場合は「在社時間内に休憩終了時刻は存在する」が、「休憩時間内に出社」しているために「休憩開始側＝False」となっている事になります。ですので「休憩終了時刻（N$7） － 出社時刻（$G8）」が休憩時間となります。
最後に、それ以外の「休憩開始側（M8）＝False」且つ「休憩終了側（N8）＝False」の場合は、数式最後の「ゼロ」となります。
この３列で一組の休憩時間計算式で、各休憩時間帯（今回８ヶ所）の各々に対して計算をします。
４－３－４．勤務時間の算出
上記の「在社時間」と「休憩時間」の合計から、実質働いた「勤務時間」を図４－１４のように算出します。
図４－１４
数式は、８行目で言えば「=I8-SUM(J8:AG8)」です。内容は「在社時間（I8）」－「休憩時間の合計（SUM(J8:AG8)）」です。
True・Falseも含めて合計計算をするのはどうか とも思いましたが、特にエラーも出ないので短い計算式にしました。もちろん「=I8-(L8+O8+R8+U8+X8+AA8+AD8+AG8)」と各セルを足してもOKです。
なお、出社だけして退社していない日については在社時間がマイナスとなるため、勤務時間もマイナスとなります。そのため表示は在社時間と同様に「*****･･･」という形になります。
５．各セルの書式など
５－１．時刻を入れるセルの書式
今回、退社時刻や休憩時刻については24時間超の計算をしています。ですのでExcelとしての表示も24時間超にするため、図５－１のように書式設定を変更しています。
図５－１
時刻を入力・表示するセルに対しては、書式をユーザー定義で「[h]:mm」としています。角カッコで「h（時間）」を囲むことで24時間を超えた表示が可能となります。
なお、図５－１の「[h]:mm」の２つ上に、似たような「[h]:mm;@」という書式も存在します。後ろの「;@」の意味は「その国・地域に応じた表示形式が設定されている場合でも、ユーザーが指定した通りの表示形式とする」という意味の様です。
しかし「年月日」は米国風に表示すると「mm/dd/yyyy」となりますが、「時分」は各国・地域で異なるとも思えませんので、今回「;@」は添付しませんでした。もし「時分の表示は日本と違う」という国・地域で使われるのであれば、「[h]:mm;@」の方を試してみて下さい。
また図４－Qの赤矢印で示したように、A列の日付の書式は「d」としました。A列には日付型としての数値（正確には、A6セルの基準の日付を１日ずつ加算している数式）が入っていますが、日付だけを表示するために、書式を「d」としています。
５－２．月末の翌月部分の処理
「大の月」「小の月」というように、月によって日数が異なります。大の月に合わせて31日までの表としてしまうと、小の月の月末部分には「翌月の勤務表」が表示されてしまうことになります。
ですので今回は、図５－２のように「表示したく無い月末部分を黒で塗りつぶす」という条件付き書式を設定しました。
図５－２
条件付き書式の適用先は、表全体でもOKですが、最小限である「増減する可能性のある29～31日の行」としました。
条件式は「=MONTH($A36)<>MONTH($A$6)」とし、表示年月の基準セル「MONTH($A$6)」の月と左端A列の日付セル「MONTH($A36)」の月を比較し、異なっている場合（＝翌月の場合）は設定書式の黒色背景に変更します。
（この条件式を成立させるためにも日付のA列は日付型にする必要があり、書式「d」を使って日付値のみにしています。）
この設定により、小の月では図５－３のように「翌月の部分は黒で塗りつぶし」されることになります。
ちょっと子供だまし的な処理ではありますが、間違って入力をすることくらいは防げる気がします。なお、もしマクロが使えるのであれば、A6セルのChangeイベントに反応させて翌月分の行を非表示にする手段も考えられます。
図５－３
６．最後に
労働時間短縮の手段として、例えば8時間定時を7時間55分定時とする方法もあります。そのような会社では、8:30始まりなのに定時終わりは17:25となってしまい、今回のような30分単位の数式が使えなくなります。
他にも、おやつ休憩（例：10分間）が散りばめられていたりすると、Excelの簡単な数式で表現できなくなってしまいます。むしろ、そのような複雑な勤務体系の会社の方が多いのかもしれません。
また今回は、非表示列に多くの数式を隠すような仕様となりました。頑張れば全ての非表示部分の数式をつなげて１つの数式にすることも可能だと思います。しかし、もし計算式の間違えを修正しようとしたり、計算ロジックを変更しようとしたりする場合には、とても大変な作業になるはずです。
列が隠せないような場合には、ユーザー定義関数を作り、その中でロジックを組み立てる方が現実的だと思います。
休憩を差し引く勤務時間計算（it-072.xlsx）
セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始）
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。
図４－１２
数式は図４－１３のように、図４－１１の①②③及びそれ以外に分けて考えます。
図４－１３
まず②の部分は「休憩開始側（M8）＝True」且つ「休憩終了側（N8）＝True」の場合です。
この場合は、在社時間内に完全に休憩時間が含まれていますので、「全ての休憩時間」が対象となります。ですので「休憩終了時刻（N$7） － 休憩開始時刻（M$7）」が休憩時間となります。
次に①の部分は「休憩開始側（M8）＝True」の場合です。②より後ろ側にありますので「休憩終了側＝False」です。
この場合は「在社時間内に休憩開始時刻は存在する」が、「休憩時間内に退社」してしまうために「休憩終了側＝False」となっている事になります。ですので「退社時刻（$H8） － 休憩開始時刻（M$7）」が休憩時間となります。
③の部分は「休憩終了側（N8）＝True」の場合です。②①より後ろの部分ですので「休憩開始側＝False」です。
この場合は「在社時間内に休憩終了時刻は存在する」が、「休憩時間内に出社」しているために「休憩開始側＝False」となっている事になります。ですので「休憩終了時刻（N$7） － 出社時刻（$G8）」が休憩時間となります。
最後に、それ以外の「休憩開始側（M8）＝False」且つ「休憩終了側（N8）＝False」の場合は、数式最後の「ゼロ」となります。 この３列で一組の休憩時間計算式で、各休憩時間帯（今回８ヶ所）の各々に対して計算をします。
４－３－４．勤務時間の算出
上記の「在社時間」と「休憩時間」の合計から、実質働いた「勤務時間」を図４－１４のように算出します。
図４－１４
数式は、８行目で言えば「=I8-SUM(J8:AG8)」です。内容は「在社時間（I8）」－「休憩時間の合計（SUM(J8:AG8)）」です。
True・Falseも含めて合計計算をするのはどうか とも思いましたが、特にエラーも出ないので短い計算式にしました。もちろん「=I8-(L8+O8+R8+U8+X8+AA8+AD8+AG8)」と各セルを足してもOKです。
なお、出社だけして退社していない日については在社時間がマイナスとなるため、勤務時間もマイナスとなります。そのため表示は在社時間と同様に「*****･･･」という形になります。
５．各セルの書式など
５－１．時刻を入れるセルの書式
今回、退社時刻や休憩時刻については24時間超の計算をしています。ですのでExcelとしての表示も24時間超にするため、図５－１のように書式設定を変更しています。
図５－１
時刻を入力・表示するセルに対しては、書式をユーザー定義で「[h]:mm」としています。角カッコで「h（時間）」を囲むことで24時間を超えた表示が可能となります。
|
なお、図５－１の「[h]:mm」の２つ上に、似たような「[h]:mm;@」という書式も存在します。後ろの「;@」の意味は「その国・地域に応じた表示形式が設定されている場合でも、ユーザーが指定した通りの表示形式とする」という意味の様です。 しかし「年月日」は米国風に表示すると「mm/dd/yyyy」となりますが、「時分」は各国・地域で異なるとも思えませんので、今回「;@」は添付しませんでした。もし「時分の表示は日本と違う」という国・地域で使われるのであれば、「[h]:mm;@」の方を試してみて下さい。 |
また図４－Qの赤矢印で示したように、A列の日付の書式は「d」としました。A列には日付型としての数値（正確には、A6セルの基準の日付を１日ずつ加算している数式）が入っていますが、日付だけを表示するために、書式を「d」としています。
５－２．月末の翌月部分の処理
「大の月」「小の月」というように、月によって日数が異なります。大の月に合わせて31日までの表としてしまうと、小の月の月末部分には「翌月の勤務表」が表示されてしまうことになります。ですので今回は、図５－２のように「表示したく無い月末部分を黒で塗りつぶす」という条件付き書式を設定しました。
図５－２
条件付き書式の適用先は、表全体でもOKですが、最小限である「増減する可能性のある29～31日の行」としました。
条件式は「=MONTH($A36)<>MONTH($A$6)」とし、表示年月の基準セル「MONTH($A$6)」の月と左端A列の日付セル「MONTH($A36)」の月を比較し、異なっている場合（＝翌月の場合）は設定書式の黒色背景に変更します。
（この条件式を成立させるためにも日付のA列は日付型にする必要があり、書式「d」を使って日付値のみにしています。）
この設定により、小の月では図５－３のように「翌月の部分は黒で塗りつぶし」されることになります。
ちょっと子供だまし的な処理ではありますが、間違って入力をすることくらいは防げる気がします。なお、もしマクロが使えるのであれば、A6セルのChangeイベントに反応させて翌月分の行を非表示にする手段も考えられます。
図５－３
６．最後に
労働時間短縮の手段として、例えば8時間定時を7時間55分定時とする方法もあります。そのような会社では、8:30始まりなのに定時終わりは17:25となってしまい、今回のような30分単位の数式が使えなくなります。他にも、おやつ休憩（例：10分間）が散りばめられていたりすると、Excelの簡単な数式で表現できなくなってしまいます。むしろ、そのような複雑な勤務体系の会社の方が多いのかもしれません。
また今回は、非表示列に多くの数式を隠すような仕様となりました。頑張れば全ての非表示部分の数式をつなげて１つの数式にすることも可能だと思います。しかし、もし計算式の間違えを修正しようとしたり、計算ロジックを変更しようとしたりする場合には、とても大変な作業になるはずです。
列が隠せないような場合には、ユーザー定義関数を作り、その中でロジックを組み立てる方が現実的だと思います。
休憩を差し引く勤務時間計算（it-072.xlsx）|
セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。 |