エクセルで深夜勤務の勤務表を作成!0時をまたぐ計算もできる!

3 min 4,198 views
エクセル初心者

エクセル初心者

深夜の勤務表を作成したいんだけど、0時をまたぐと勤務時間の計算ができなくなっちゃって困ってるんだ。 どうすればいいかなぁ?

エクセル初心者

エクセル初心者

日勤の勤務表や、終了時間が24時を越えなければ、ちゃんと勤務時間の計算ができるんだけどね・・・困ったなぁ。

勤務時間の計算は、0時をまたぐのであれば計算式を変えたほうがいいよ! 日勤の勤務表と、深夜勤務の0時をまたぐ勤務表とでは計算も変わってくるよ。

Dr.オフィス

Dr.オフィス

今回は、0時をまたぐ勤務表の勤務時間を計算する方法を解説します。

出勤簿の作成方法についてはこちら≫エクセルで時間の計算をする時に使える関数は?!表示がうまくいかない方も必見!も参考にされてみてください。

※本記事は『OS:Windows10』画像は『Excelのバージョン:Office365』を使用しています。

エクセルで深夜勤務の勤務表作成

勤務表を作成しましょう。

勤務時間を求める
勤務時間を求める

↑出社時間・退社時間・休憩時間を入力し、F列に『勤務時間』を計算します。

F3セルには『退社時間-出社時間-休憩時間(=D3-C3-E3)』を入力しています。

シャープ
シャープ

↑夜中の0時を超えていない退社時間であれば、『退社時間-出社時間-休憩時間』の計算で勤務時間を求めることができます。

しかし、夜中0時を超えた退社時間になると『####(シャープ)』で表示されてしまいます。

時間や日付の計算は『負の値』になると表示できずに『####(シャープ)』になります。

勤務時間を求めるための方法を解説していくので、ぜひ参考にしてください。

基本的な勤務表を作成するために『カレンダーの作成方法』もおすすめ記事になります。

数式に『+1』を入れる

5時間を表示させるには
5時間を表示させるには

↑2日は『22時から4時まで勤務』しています。

1時間の休憩があるので、勤務時間は『5時間』になるはずですが『####(シャープ)』で表示されています。

F4セルの勤務時間が『負の値』になったため表示できない状態です。

『負』にならないようにするために、『24時間分』を退社時間に加算してみましょう。

退社の時間に24時間を足して考える

4時は28時と考えてみる
4時は28時と考えてみる

↑退社時間の『4時』を『28時』と考えてみましょう。

『退社時間-出社時間-休憩時間』は『28時-22時-1時間』になり、勤務時間は『5時間』になります。

24:00をたしてみよう
24:00をたしてみよう

↑『=D4-C4-E4』に『24:00』を足してみます。

計算できない
計算できない

↑『=D4+(24:00)-C4-E4』では数式に問題があり計算できません。

セルを用意する
セルを用意する

↑G2セルに『24:00』を入力したセルを用意します。

G2セルを参照する
G2セルを参照する

↑このG2セルを参照しましょう。

『=D4+G2-C4-E4』になります。

勤務時間を求めることに成功
勤務時間を求めることに成功

↑勤務時間を求めることができました。

勤務時間を求めることができたね! でも『24:00』と入力したセルを1つ作らないといけなくなるので注意してね。

Dr.オフィス

Dr.オフィス

24時間はシリアル値で表すと『1』になる

24時間はシリアル値で『1』
24時間はシリアル値で『1』

↑実は『24:00』はシリアル値で『1』と同じになります。

シンプルに『=D5+1-C5-E5』と計算しても、勤務時間を求めることができます。

オートフィルでコピー
オートフィルでコピー

↑『24時間』を足す計算は、0時をまたいでいなくても計算できます。

F列にオートフィルでコピーして、不要なセルの数式は削除しましょう。

IF関数で空白は表示しない
IF関数で空白は表示しない

↑IF関数を使って、『空白』を表示すると削除する手間も省けるため仕事効率が上がります。

F3セルに『=IF(C3=0,””,D3+1-C3-E3)』を入力し、オートフィルでコピーすると『出勤時間の入力がないセルは空白』で表示させるようになります。

IF関数についてはこちら≫エクセルの【IF関数】を使い「#N/A」や「0」などを表示させず空白セルにする!を参考にされてみてください。

表示形式を変更して入力する

勤務時間を求める
勤務時間を求める

↑22時に出勤し、4時に退社、休憩1時間の場合の勤務時間を『=D3-C3-E3』で求めます。

シャープで表示される
シャープで表示される

↑確定させると『####(シャープ)』になってしまいます。

『4時-22時-1時間』なので、勤務時間が『負(マイナス)』になっているため表示できません。

4時を28時で入力する
4時を28時で入力する

↑退社は『4時』なので、入力する退社時間を『28:00』にしてみましょう。

勤務時間が表示される
勤務時間が表示される

↑勤務時間は『28時-22時-1時間』の『5時間』が計算され表示できました。

マイナスの時間ではないので、きちんと表示されます。

28:00の表示
28:00の表示

ショートカットキー『Ctrl+1』でセルの書式設定から『表示形式』を開きます。

ユーザー定義で種類を『[h]:mm』に設定すると『28:00』と表示されます。

28時だけど4:00の表示
28時だけど4:00の表示

↑ユーザー定義で種類を『h:mm』に設定すると『4:00』と表示されます。

表示形式の種類を変更して、見やすい時間表示に設定しましょう。

ExcelドクターがおすすめするExcel本はこちら

カテゴリー:

20万人以上が参考にしたエクセルドクターで最も人気の記事

エクセルで家計簿を作る方法

ワード初心者

ワード初心者

エクセルで本格的な家計簿を作ってみたいんだけど詳しく解説してる本とかがないんだよね・・・
徹底的に解説してほしいな!

エクセルで本格家計簿の作り方なら私に任せて!過去に20万人以上のユーザーが参考にした解説記事を紹介するね♪

Dr.オフィス

Dr.オフィス

エクセルを少し使えるようになってきたら、本格的な家計簿を作ってみるのがとてもおすすめです。

収支シートの出来上がりイメージ
収支シートの出来上がりイメージ

基本機能をしっかりと使って作るから、エクセルをマスターするのにも家計簿づくりはぴったり!

20万人以上が参考にしたエクセルでの本格的な家計簿の作り方はこちら↓↓

【エクセル家計簿の作り方】月ごとに入力して合計を反映させれば見やすくなる!

関連記事