Excel。24時間オーバーの時と分を別々のセルに表示させるには
＜HOUR+DAY&MINUTE関数＞
Excelで時間関係の資料を作ろうとすると、思いのほか作れないことがあります。今回もその中の一つだと思います。
まずは、下の表をご覧ください。
勤務時間の合計がD7に算出されています。
この勤務時間の合計を時間と分に分けて表示している資料です。
見た目、簡単にできそうですが、一筋縄ではいかないのです。
では、一つずつ確認していきましょう。
最初の確認は、D7の勤務時間の合計を算出するところからです。
時間の合計ですので、SUM関数を使用して算出してみると、
12:30という勤務時間の合計が算出されました。
Excelでの時間計算でお馴染みの24時間オーバー問題。
Excelは1日をシリアル値で1としていて、
24時間以上を越えた場合は、何日目の何時として、
そのうちの、時間のみを表示してしまいます。
そこで、登場するのが、【表示形式】でしたね。
では、D7をクリックして、
セルの書式設定ダイアログボックスを表示しましょう。
セルの書式設定ダイアログボックスは、
Ctrl+1というショートカットキーが楽ですね。
分類の「ユーザー定義」にして、
種類を、[h]:mmと設定してOKボタンをクリックしましょう。
これで、24時間以上の表示が出来ましたね。
さて、ここからが本題。
B10に時間である。36というのを表示させたいわけです。
文字でないので当然、LEFT関数を使うわけにはいきませんね。
そこで、時間を抽出する関数である、HOUR関数を使ってみましょう。
B10をクリックして、HOUR関数ダイアログボックスを表示しましょう。
シリアル値にD7を設定してOKボタンをクリックしましょう。
あれれ、36ではなくて、12と算出されてしまいましたね。
実は、HOUR関数。
これも24時間オーバーに対応していないのです。
あくまでも、24時間を取り除いた数値なわけです。
そこで、算出された時間に、日数分を24倍した数値を足せば、
時間を算出することが出来ます。
日数分を算出するためには、DAY関数を使う必要があります。
これで、何日分の何時間の何日分が算出されていますので、
この日を時間という単位に変換しますので、24倍するわけです。
早速、B10の数式を修正していきましょう。
あれれ？数式を
=HOUR(D7)+DAY(D7)*24
と修正したにもかかわらず、
864:00と意味不明の数値が表示されてきました。
ここで、慌ててはいけませんね。
これもExcelの時間トラブルの一つですね。
表示形式が時間になっているので、表示形式を標準に戻してみましょう。
これで、36と算出することができましたね。
あとは分ですね。
分はMINUTE関数を使えば簡単に算出することが出来ますね。
C10の数式は、
=MINUTE(D7)
という数式が設定されています。
これで、24時間オーバーの時間と分を
別々のセルに表示させることができましたね。
Excelの時間関係は、一癖ありますので、ご注意ください。
