エクセル勤怠管理で簡単に時刻表示する７つの方法

エクセルで勤怠管理するときに、時間表示のやり方がわからない方は多いです。実は時間表示は書式設定を理解することで、思い通りに時間表示できるようになります。

書式設定とは、「入力された数値をどのように表示するのか」を設定するものです。例えば、勤務が深夜までおよび、退勤時間が午前３時になったとき、「27:00」と表示したい、または下の図のように残業時間の合を24時以上の時刻で表示したいことがあります。

このようなときは、書式設定で下の図のように[h]:mmと設定します。

書式設定には、この他にも種類があり、さまざまな時間表示に対応できるようになります。書式設定を理解することで、思い通りに時間表示でき、事務作業の効率化につながります。そこでこのページでは、書式設定について詳しく解説していきます。

 

「書式設定」が必要な理由

なぜ書式設定が必要なのかというと、先述したように、退勤時間を「27:00」と表示したい場合や、残業時間の合計を表示したいとき、初期設定ではうまく表示できない場合があるためです。

他にも残業時間を管理するときは、誰が何時間残業しているか、規定の時間まであとどれくらいなのかを、わかりやすく表示すると管理しやすいためです。

例えば下の図のように、残業時間が30時間を超えたらセルを黄色に、42時間を超えたらオレンジ色、72時間を超えたら赤にすると、ひと目で状況を把握することができます。

他にも、曜日によって自動的にセルの色を変えたり、日付と曜日を同時に表示したりできます。つまり、書式設定により、勤怠管理を効率化できるのです。

【基礎編】書式設定の方法

それでは、書式設定の基本的なやり方を解説します。ここでは、時刻が一桁と小数点の数字（シリアル値）で表示されてしまったときに、時刻表示にもどす手順について解説します。

1. 書式設定したい範囲を選択する

2. 「セルの書式設定」をクリックする

選択したセルの上で右クリックして「セルの書式設定」をクリックします。

3. 表示したい書式を選ぶ

「セルの書式設定」をクリックすると下の図のようなウィンドウ（ダイアログ）が現れます。「表示形式」、「時刻」、「種類」の順に選択し、最後にOKボタンをクリックするか、エンターキーを押します。

4. 完成

シリアル値が時刻表示されました。

以上の手順が書式設定の基本的な方法です。

【応用編】書式設定の方法

時刻表示の初期設定では、「時」が一桁とき（9:01など）は、そのまま「9:01」と表示されます。しかし、「時」二桁（09:01）で表示したいことや、「秒」まで表示したいことがあります。

そのようなときは、下の図のように「種類(T)」にh（hour:時）やm（minutes:分）、s（seconds:秒）を組合せて入力します。

セルに「9:3:6（9時3分6秒）」と入力したときを例として、文字の組み合わせで、実際に表示される時刻を以下の表にまとめました。

24時以降を表示する方法

深夜勤務した時や、時間を合計する場合、24時以降を表示したいことがあります。例えば、「午前３時」を「27:00」と表示したいときや、１ヶ月間の残業時間を表示したいときなどです。

先ほどの「3. 表示したい書式を選ぶ」の手順では24時以降を表示できません。しかし、書式設定で[h]:mmと入力することで、24時以降を表示することができるようになります。

24時間以上を表示できるようになると、正しく時間管理ができるようになります。勤務が深夜までおよんだ場合や、時間を合計するときは、この設定を行わないと正しく表示されないことがあるため、必須の設定です。

[]（角括弧）で囲むと24時以降を表示できる

時刻表示文字（h、m、s）を[]（角括弧）で囲むと、24時あるいは60分、60秒以上を表示できるようになります。角括弧キーは下記の場所にあります。

主な組合せを下記の図にまとめました。例として、「120:25:06」のときに表示される組み合わせです。

 

簡単に曜日表示する方法

勤怠管理では、日付の横に曜日を表示させたいことがあります。よくある方法は、下の図のように、曜日を入力するセル別に設け、手入力する方法です。

しかし、この方法は入力に手間がかかり、入力ミスも起きてしまうため、あまり良い方法ではありません。そこでオススメの方法は、書式設定によって自動的に曜日を表示させる方法です。書式設定すると、下の図のように、日付の横に曜日を表示することができます。

こうすることで、入力ミスを防ぐことができ、管理しやすくなります。また、日付とは別のセルに曜日を入力する必要が無いので、業務の効率化になります。

1. 書式設定したい範囲を選択します。

2. 書式設定の「種類(T)」に「yyyy/m/d/ (aaa)」と入力します。

分類(C)のなかの「ユーザー定義」を選択し、種類(T)に「yyyy/m/d (aaa)」と入力し、OKボタンをクリックします。

時刻と同じように、書式設定の「種類(T)」に、特定の文字を入力することで、日付の表示方法が変わります。曜日の書式設定に使える文字の組み合わせを以下の表にまとめました。（入力値は2020/12/1）

 

曜日によって文字の色を変える方法

勤怠管理では、土曜日・日曜日など特定の曜日のときだけ、下の図のように、文字の色を変えて表示したいことがあります。

このとき、ひとつひとつ目で確認して文字の色を変えると、大変な手間がかかってしまいます。そこで以下の手順のように条件付き書式設定することで、特定の曜日のときだけ色を変えることができ、業務の効率化になります。

1. 書式設定したい範囲を選択する

2. ホームタブの「条件付き書式」、「新しいルール」をクリックする

3. 書式ルールの編集で「数式を使用して、書式設定するセルを決定」をクリックし、数式を入力する

下の図の「次の数式を満たす場合に値を書式設定(O)」とは、入力した数式が特定の値になったときだけ書式設定を行うための設定で、「=WEEKDAY(A2)=1（WEEKDAY関数の答えが1）」のときだけ書式設定する、というものです。

「WEEKDAY関数」とは

4. 設定したい色を選択する

曜日で変えたい色を選択します。見やすい色にしましょう。

色を選択したら、「書式のルール設定の編集」のOKボタンをクリックして完成です。ここでオススメの色は、土日休みの場合であれば、日曜日が赤、土曜日が青です。なぜなら、多くのカレンダーでこの色の組み合わせだからです。こうすることで、誰が見てもわかりやすい勤怠管理表になります。

以上の手順で、日曜日のときに文字を赤くする設定になりました。同様に、土曜日を青くしたい場合は、数式を「=WEEKDAY(A2)=7」にします。

残業時間数によって自動的にセルの色を変える方法

先述したように、残業時間数によって、自動的にセルの色を変えることで勤怠管理を効率化することができます。例えば、規定の残業時間上限まであとどれくらいか、規定を超えた従業員がどれくらいいるかを、ひと目で把握することができます。

下の図の右表が、セルの値によって自動的に色が変わるように書式設定したものです。ここでは、セルの値が32時間以上で黄色、42時間以上でオレンジ色、72時間以上でセルの色が赤く場合につてい解説します。書式設定の手順は以下のとおりです。

1. 書式設定したい範囲を選択し、条件付き書式設定のなかの「ルールの管理」をクリックする

２. 書式ルールを設定する

ルールの管理をクリックすると下の図のようなダイアログが現れるので、「書式ルールの表示(S)」が「現在の選択範囲」になっていることを確認し、「新規ルール」をクリックします。

下の図の順番で設定していきます。「3.  32:00と入力」の部分は、60進法で時間管理している場合の設定です。10進法で管理している場合は、「32」と入力します。次の手順「4. 書式(F)」ではセルの色を設定します。

特別条項付き三六協定を結んでいても45時間（変形労働制の場合は42時間）を超える時間外労働ができるのは、年間6回までです。そのため、45時間より10時間ほど前を最初の警告とします。黄色がよいでしょう。

3. 次の書式ルールを設定する

続いて、特別条項である45時間（変形労働制の場合は42時間）を超えたときの警告色を設定するため、再度「新規ルール(N)」をクリックします。

時間設定部分に「42:00」と入力し、その他の部分は先ほどと同様に設定します。セルの色は黄色と赤の中間であるオレンジ色がよいでしょう。

「次の値より大きい」とは？

42時間の設定ができたら、最後に72時間を超えたときの警告色を設定します。過労死ラインといわる80時間になってから警告を出しても遅いため、それより10時間程度前に最終警告を出します。時間設定部分を「72:00」、他の手順はこれまでと同様です。警告色は赤がよいでしょう。

下の図のように設定できたら、OKボタンをクリックして完成です。

注意点

下の図のように、上から黄色、オレンジ色、赤の順で並んでいると、上手くいきません。

黄色、オレンジ色、赤の並び順だと、下の図のように黄色しか書式設定されません。

 

この並び順は、書式を設定する（この例ではセルに色を塗る）優先順位です。上の図は、黄色が一番上に来ていますので、黄色を最優先に書式設定してしまいます。並び順は、表示順を変える矢印ボタンで変えることができます。

書式設定をコピーして設定の手間を省く方法

これまでの解説では、書式設定したい範囲をあらかじめ選択してから設定しました。しかし、実務では書式設定したい範囲が変わることはよくあります。ただその都度、設定したい範囲を選択してから設定していると手間がかかってしまいます。

書式設定は、コピーして貼り付けることで同じ設定をコピーでき、設定の手間を省くことができます。ここでは、例として条件付き書式設定のコピーを解説しますが、条件付き書式設定に限らず、時刻表示の書式設定などでも同様の手順でコピーして貼り付けできます。

1. 書式設定されたセルを選択し、右クリックで表示されるメニューのなかの、「コピー(C)」をクリックします。

2. 同じ条件付き書式に設定したい範囲を選択し、右クリックし「形式を選択して貼り付け(S)」をクリックします。

3. 下の図のように「書式(T)」を選択し、OKボタンをクリックします。

以上の手順で、書式設定を簡単にコピーすることができます。

まとめ

ここまでご説明してきた「書式設定」によって、勤怠管理にかかせない「時間表示」を思い通りにすることができるようになります。今回解説したものは、基本的な内容ですが、基本の組み合わせによってあらゆるパターンに対応することができます。

勤怠管理業務を効率化するためにも、ここでお伝えしたテクニックを駆使し、効率よく事務作業を行いましょう。