MENU
Excel

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

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

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

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

この記事の目次
  1. LINEでExcelを気軽に学べる
  2. 抑えておきたい4つのポイント
  3. 時間の変換紹介
  4. 事例紹介
  5. まとめ
期間限定でChatGPT✖️Googleスプレットシートのコンテンツ配布中！

LINEでExcelを気軽に学べる

しんたろ。
しんたろ。
Excel歴10年以上のプロ
Profile
【実績】
□2016年
海外拠点において、Excelでの業務改善による納期遵守率50%→100%達成。
□2018年
自動化を促進させ、自身の残業時間を年間240時間削減成功。
□2023年
├ブログとTwitter開始
├Excel無料相談会→即日満席
└ChatGPT×Googleスプレットシート配布で1週間で35万インプ達成

仕事ができなくて毎日上司に叱責されていた私がExcelを学んだら評価され残業を大幅に削減！現在は職場改善チームに所属し、PythonやRPAなどのツールで会社全体の改善をしています。仕事の依頼はTwitterのDMまで！
プロフィールを読む
YouTubeLINEContact

抑えておきたい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」だけ表示させたりするなど、よく使用します。

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

【労働時間管理が楽々！】エクセルシフト表＆カレンダーの使い方
【労働時間管理が楽々！】エクセルシフト表＆カレンダーの使い方

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

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

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

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

時間の変換紹介

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

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

秒数を時分に変換
#時分 表示
=TEXT(A1/86400,"hh時間mm分")
#:: 表示
=TEXT(A1/86400,"hh:mm")

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

秒数を時分秒に変換
#時分秒 表示
=TEXT(A1/86400,"hh時間mm分ss秒")
#::: 表示
=TEXT(A1/86400,"hh:mm:ss")

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

秒数を分秒に変換
#分秒 表示
=TEXT(A1/86400,"mm分ss秒")
#:: 表示
=TEXT(A1/86400,"mm:ss")

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

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

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

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

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

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

#INT関数の説明
=INT(数値または数式)

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

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

#『分』を追加
=INT(A1)&"分"
分に変換
「165秒」から『秒』を取り出すと何秒？
→MOD関数を使用

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

#MOD関数の説明
=MOD(数値, 除数)

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

#秒を追加
=MOD(A1,60)&"秒"
秒に変換
『分』と『秒』を合体させる

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

#分と秒
=INT(A1/60)&"分"
=MOD(A1,60)&"秒"
#結合
=INT(A1/60)&"分"&MOD(A1,60)&"秒"
分と秒を結合

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

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

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

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

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

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

#日 表示
=ROUNDDOWN(A2/24,0)
ROUNDDOWN関数を使用した日時
28時間から「日」を引いた「時間」を取り出すには？
→こちらもROUNDDOWN関数を使用

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

#時間だけを取り出す
=A1-(ROUNDDOWN(A1/24,0)*24)
ROUNDDOWN関数を使用した日時×24時間
STEP1とSTEP2を結合

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

=ROUNDDOWN(A1/24,0)&"日"&A1-(ROUNDDOWN(A1/24,0)*24)&"時間"
時間を日時に変換

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

事例紹介

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

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

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

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

A：日数（３６５）に２４時間を掛ける。

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

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

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

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

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

まとめ

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

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

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

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

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

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

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

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

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

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

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

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

Read more
ABOUT ME
しんたろ。
しんたろ。
Excel歴10年以上のプロ
YouTubeLINEContact
【実績】
□2016年
海外拠点において、Excelでの業務改善による納期遵守率50%→100%達成。
□2018年
自動化を促進させ、自身の残業時間を年間240時間削減成功。
□2023年
├ブログとTwitter開始
├Excel無料相談会→即日満席
└ChatGPT×Googleスプレットシート配布で1週間で35万インプ達成

仕事ができなくて毎日上司に叱責されていた私がExcelを学んだら評価され残業を大幅に削減！現在は職場改善チームに所属し、PythonやRPAなどのツールで会社全体の改善をしています。仕事の依頼はTwitterのDMまで！
Recommend
こんな記事も読まれています！
記事URLをコピーしました