使い方

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

エクセル初心者
エクセル初心者
深夜の勤務表を作成したいんだけど、0時をまたぐと勤務時間の計算ができなくなっちゃって困ってるんだ。
どうすればいいかなぁ?
エクセル初心者
エクセル初心者
日勤の勤務表や、終了時間が24時を越えなければ、ちゃんと勤務時間の計算ができるんだけどね・・・困ったなぁ。
Dr.オフィス
Dr.オフィス
勤務時間の計算は、0時をまたぐのであれば計算式を変えたほうがいいよ!
日勤の勤務表と、深夜勤務の0時をまたぐ勤務表とでは計算も変わってくるよ。

今回は、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』になります。

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

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

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



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」などを表示させず空白セルにする!を参考にされてみてください。

タイトル
エクセルの【IF関数】を使い「#N/A」や「0」などを表示させず空白セルにする!エクセルではさまざまなエラー値が表示されることがあります。【IF関数】や【IFERROR関数】を使い『0』や『#N/A』などを非表示にする方法を解説します。不要な数値やエラー値は表示させないようにしてみましょう。ちょっとした関数を使うことで、すっきりとした見栄えの良い資料などを作成できます。...



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

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

↑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』と表示されます。

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

ABOUT ME
Dr.オフィス
Dr.オフィス
エクセルの使い方や応用法を日々研究しています。 初心者から上級者までわかりやすく解説していきます。 エクセルについてわからないことや気になることなどございましたら是非、コメントやお問い合わせからご質問ください。