Excel

【事例有り】Excelで秒や分で表示された値を自在に変換する術

しんたろ。
この記事をみるとわかること
  • 事例を見ながら時分秒の取り扱い方がわかる
  • 場面によって必要な関数の使いどころがわかる
  • セルの書式設定で表示する方法がわかる
  • シリアル値の意味がわかる

「セルに『○秒単位』で入力されているデータを『○分や○時間』で表示したい」
Excelで時間を取り扱う際の覚えておきたい知識や、事例をもとに紹介します。

目次から見たい項目に飛んでくださいね♪

抑えておきたい4つのポイント

抑えておきたいポイント①:シリアル値

日付対応するシリアル値
1900/1/1 0:00:001
1900/1/1/12:00:001.5
1901/1/1 0:00:00367
2022/11/16 0:00:0044881
時分秒シリアル値に変換する計算対応するシリアル値
1秒1秒 ÷ ( 24hour × 60min × 60sec )0.000011574
1分60秒 ÷ ( 24hour × 60min × 60sec )0.000694444
60分60分 ÷ ( 24hour × 60min )0.041666667
24時間24時間 ÷ 24hour1

そもそもシリアル値が使われる理由は?

1900/1/1 = 1」から始まる数値で表現しておくと、単純に足し算や引き算が簡単におこないやすくなります。

曜日」や「うるう年」など特殊な計算もシリアル値だと計算がしやすいので、Excelにとっては都合が良いんです。

一日の秒数が『24時間 × 60分 × 60秒 = 86,400秒』ということは覚えておきましょう!

抑えておきたいポイント②:セルの書式設定

時間変換のセルの書式設定説明
種類表示結果
h0〜23(時間)
hh00〜23(時間:2桁表示)
m0〜59(分)
mm00〜59(分:2桁表示)
s0〜59(秒)
ss00〜59(秒:2桁表示)
h AM/PM 例)4:00:004 AM
h:mm:ss A/P 例)16:10:104:10:10 P

「セルの表示形式」を選択すると、簡単に時間の表示を変更できます。

ただし、表示したい形式にセルの値を合わせないといけません。

抑えておきたいポイント③:時間を表示させる関数

時間を表示する関数対象
YEAR関数
MONTH関数
DAY関数
HOUR関数
MINUTE関数
SECOND関数
時間を表示する関数たち

こちらの関数は、入力されているセルから関数で抜き取ることができます。

カレンダー作成時に「DAY」だけ表示させたりするなど、よく使用します。

カレンダーなどの作成方法はこちら

[徹底解説]Excelで月間シフト表やカレンダーを自動化する方法
[徹底解説]Excelで月間シフト表やカレンダーを自動化する方法

抑えておきたいポイント④:TEXT関数で自在に表示

セル値種類表示形式結果
2022/11/18yyyy2022
2022/11/18mm11
2022/11/18dd18
2022/11/18曜日aaa
aaaa

金曜日

TEXT関数を使用すると、様々な形式で表示をさせることが可能になります。

「日付」はもちろん「曜日」表示まで出来るんです!

時間の変換紹介

ここでは図にあるように、「A2」にある値を変換しています。
事例にある関数をコピペして使用してください。

「○秒」→「○時○分)」に変換

秒数を時分に変換

「○秒」→「○時○分○秒)」に変換

秒数を時分秒に変換

「○秒」→「○分○秒)」に変換

秒数を分秒に変換

INT関数とMOD関数を用いた方法

使用する関数内容
INT関数最も近い整数に切り捨てる
MOD関数指定された値の余りを出す
分と秒を結合

『INT関数、MOD関数』を使用して、上図のC列「秒数」をD列にて「分/秒」に変換しています。

分解して考えると簡単なので、下記で詳しく説明します。

労災0にする本の輪読時間「165秒」から『分』を取り出すと何分?
→INT関数を使用

INT関数は小数点以下を切り捨て整数にする関数です。

165秒から『分』だけ取り出すには
165秒÷60秒で良いです。が余りがいりません。INT関数を使うと『2』となります。

INT関数のあとに文字で『分』を入れる時は、&を使うと『2分』と表示できます。

分に変換
「165秒」から『秒』を取り出すと何秒?
→MOD関数を使用

MOD関数は余りを出してくれる関数です。

先程と同様に、秒数に&を付けて『45秒』と表示しましょう。

秒に変換
『分』と『秒』を合体させる

『○分○秒』と表示したいので、先程の2つを『&』で結合しましょう。

分と秒を結合

お疲れさまです!完成です♪

○時間を日数表示する方法

使用する関数内容
ROUNDDOWN関数小数点を切り捨て
時間を日時に変換

「セルA2の28時間」を日時に変換しています。
「ROUNDDOWN関数」を用いて表示が可能になります♪

28時間から「日」を取り出すには?
→ROUNDDOWN関数を使用

28時間/24時間=1.66666・・・となるので
ROUNDDOWN関数で切り捨てて「=1」と表示します。

ROUNDDOWN関数を使用した日時
28時間から「日」を引いた「時間」を取り出すには?
→こちらもROUNDDOWN関数を使用

28時間−ROUNDDOWN(28時間/24時間,0桁)×24時間
これで「日」を引いた「時間」を取り出せます。

ROUNDDOWN関数を使用した日時×24時間
STEP1とSTEP2を結合

STEP1に「日」
STEP2に「時間」
「&」を使用して結合したら完成です。

時間を日時に変換

分解して考えると簡単なので是非活用してみてくださいね♪

事例紹介

実際に職場で使用する事例を用いて説明をします。

あえて問題形式にしているので、時間変換のおさらいがてらチャレンジしてみてくだい♪

無災害「日数」を「時間」に変換する方法

無災害日数を時間換算
Q
Q:無災害日数→時間に変換する方法は?
無災害日数を時間換算の答え

A:日数(365)に24時間を掛ける。

今まで関数を使用していたので引っ掛け問題のようになりましたが、単純に24時間掛ければ答えが出ますね♪

設備の停止「秒数」を「時間」に変換する方法

設備停止秒数を時間換算
Q
Q:設備の停止秒数→時間に変換する方法は?

A:秒数を時間に変換して、TEXT関数で表示。

TEXT関数は非常に便利なので、是非覚えてくださいね!

まとめ

この記事をみるとわかること
  • 事例を見ながら時分秒の取り扱い方がわかる
  • 場面によって必要な関数の使いどころがわかる
  • セルの書式設定で表示する方法がわかる
  • シリアル値の意味がわかる

キーワードは分解!したいことを明確にして、正しく時間を変換しましょうね♪

全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説

しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。

覚えておくべき関数や操作方法を徹底解説!

2
Concept
Excelを最短で
習得する方法を解説

「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。

本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。

3
Concept
Excelが大活躍!
会社員でも不就労所得を作れる。

「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。

記事を書く際の様々な分析にExcelがとても役立ちます。私は2023年末までにブログで脱サラする予定です!

あなたもブログで脱サラしましょう。


ABOUT ME
しんたろ。
しんたろ。
脱サラ目指す会社員
デスクワークに追われ、仕事が極端に遅かった私がExcelを学んだら会社から評価され残業も大幅削減を実現!早期FIREするための副業もしています。 あなたの人生も好転させたい!ぜひ本ブログをご活用ください。
記事URLをコピーしました