Excel。時間から時刻を表示するHOUR関数ですが、表示形式と連動していません。 【time】

Excel。時間から時刻を表示するHOUR関数ですが、表示形式と連動していません。

＜HOUR関数＞

時間から時刻を表示することができるのがHOUR関数です。

B2には、

=HOUR(A2)

という数式を設定しています。

オートフィルで数式をコピーしています。

B2は13:15なので、13と表示されているのはいいのですが、B3とB4はどうかというと、A列に表示されている時刻と、異なっています。

B3は13。

B4は2。

要するに、表示形式とは連動しておりません。

A3は、AM/PM表示にしていますが、HOUR関数は24時間表示で対応しています。

また、A4のように26:15というような24時間以上の表示にも、連動していないので、注意が必要です。

Excel。時刻から"時"を算出するのが、HOUR関数です。【HOUR】

Excel。時刻から"時"を算出するのが、HOUR関数です。

＜関数辞典：HOUR関数＞

HOUR関数

読み方： アワー　　

分類： 日付時刻 

HOUR(シリアル値)

時刻から"時"を算出する　

Excel関数辞典　VOL.35。HLOOKUP関数～HYPGEOM.DIST関数

＜Excel関数＞

今回は、HLOOKUP関数～HYPGEOM.DIST関数までをご紹介しております。

HLOOKUP関数

エイチルックアップ

横方向の表からデータを検索して抽出する

HLOOKUP(検索値,範囲,行番号,検索方法)

HOUR関数

アワー

時刻から"時"を算出する

HOUR(シリアル値)

HYPERLINK関数

ハイパーリンク

他のドキュメントへのリンクを作成

HYPERLINK(リンク先[,別名])

HYPGEOMDIST関数

ハイパージオムディスト

超幾何分布の確率を算出

HYPGEOMDIST(標本の成功数,標本の大きさ,母集団の成功数,母集団の大きさ)

HYPGEOM.DIST関数

ハイパージオム・ディスト

超幾何分析の累計確率か確率密度を算出　Excel2010以降

HYPGEOM.DIST(標本の成功数,標本の大きさ,母集団の成功数,母集団の大きさ,関数形式)

Excel。24時間オーバーの時と分を別々のセルに表示させるには【Hour】

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の時間関係は、一癖ありますので、ご注意ください。