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

3 min 1,435 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本はこちら

カテゴリー:

本を読みたいけど忙しくて読めない

そんな人におすすめなのがAmazonオーディオブック『Audible』

オーディブルのバナー
ワード初心者

ワード初心者

本を読んでビジネスに役立つ情報を身に着けたいけど、忙しくてなかなか本を読む時間がないんだよね・・・

社会人になるとなかなか本を読む時間って作りにくいよね。

そんな時におすすめのサービスがAmazonオーディオブックの『Audible』だよ!

Dr.オフィス

Dr.オフィス

本は読むから聴くに変わってきています。

聴くだけなら通勤中や運転中などちょっとしたときにできますね。

オーディオブックを提供しているサービスはいくつかありますが、一番のおすすめはAmazonオーディオブックの『Audible』です。

なぜ、おすすめかと言うと、最初に登録したときに1冊無料で聴くことができるからです。

その1冊は、『Audible』内のすべての本から選ぶことができます。

たとえばこちら

Audible』のビジネス書ランキングでもトップの「アウトプット大全」が無料で聴けちゃいます

学ぶだけでなくアウトプットすることで結果に変えることができることを解説した本で、ビジネスマンなら必ず読んでおきたい1冊ですね。

他にもこちら

自分の本当の強みを見つけることによって、自分の適職を見つけ出す。

それを可能にしてくれる本が「適職の結論 (あなたが気づいていない 「本当の強み」がわかる)」です。

他にもたくさんのビジネス本があって、そのなかから1冊を無料で聴くことができます。

しかも、すでに配信が決定しているオーディオブックもあり、その予約もできます。

まったく新本が追加されないといった心配もありません。

ビジネス本の他にも、語学学習本や小説、映画のノベルティー本などなんと40万冊のオーディオブックが用意されています。

ビジネス本をすぐに聴きたいという方はぜひ無料体験をどうぞ。

Audibleなら、なかなか時間の取れない忙しい方でも運転、通勤、家事、育児など普段の生活の中で学ぶことができます。

空いてる耳を使ってビジネス力をアップしましょう。

Dr.オフィス

Dr.オフィス

30日以内に解約すれば完全に無料で1冊見ることができます。

もし気に入って継続したとしても解約はいつでもでき、購入した本はいつでも聴くことができます。

Amazonの超太っ腹なAudible無料体験でビジネス力を高めましょう。

\登録は簡単5分/

関連記事