Excel(エクセル)では数値だけでなく時刻や時間の足し算や引き算も可能です。
労働時間や勤務時刻を管理する総務部などでは、エクセルで労働時間を管理する必要があります。
今回は時刻・時間の足し算と引き算の方法と、計算結果が２４時間を超える場合や日をまたぐ場合の対処方法を解説します。
この記事で学習できること
- 時間・時刻の足し算・引き算
- 計算結果を分や秒で表示させる方法
- 時間・時刻の足し算が２４時間を超える場合の表示方法
- 日をまたぐ時刻の引き算の方法
夜の19時出勤、翌朝の5時退勤の場合、勤務時間ってどう引き算すればいいの？
エクセルで時間を表示させる方法
まずはエクセルのセルに入力した時間を【15:20】と表示する方法を紹介します。
手順1：セルを選択する
まず表示方法を変更したいセルを選択します。
これから利用する予定の空白セルも一緒に選択しても構いません。
手順２：「セルの書式設定」を選択
右クリックし、右クリックメニューの「セルの書式設定」を選択します。
手順３：「時刻」の表示形式を選択
「セルの書式設定」画面の「表示形式」タブを開きます。
「分類」リストの中から「時刻」を選択し、さらに「種類」リストの中の「13：30」を選択し「OK」で画面を閉じます。
手順４：表示の変更を確認
「〇〇時△△分」と表示されていたセルが「〇〇：△△」と表示形式が変更されます。
このように「セルの書式設定」画面から時間の表示形式を自由に変更することが出来ます。
「15:30」といった表示以外にも、「19:30:50」とった秒を追加した表示も可能です。
他にも様々な表示形式が「セルの書式設定」画面上で用意されています。
下の記事でセルの表示形式を変更する方法を詳しく紹介していますので、合わせて参考にしてみてください。
時間を入力する方法
セルの表示形式を変更したセルに時間を入力する際は、時間と分の間に「:」(コロン）を入れて入力してください。
設定したセルの表示形式に合わせて、体裁が整えられます。
セルに時間を入力する方法
時間の足し算の方法
時間や時刻の足し算は、例外的な場合を除いて数値の場合と方法は同じです。
足し算の結果が２４時間を超える場合に、どのようにセルに表示させたいかによって対応が異なります。
足し算の方法(２４時間以内)
時間や時刻の足し算も、演算記号「＋」を使うことで以下のように足し算することが出来ます。
「Enter」キーで関数を確定すると、「10:15」＋「10:15」の結果が「16:05」と正しく表示されます。
足し算や引き算が苦手な方は、以下の記事で四則演算の方法を詳しく紹介していますので、参考にしてみて下さい。
続いて、下の図のように「10:00」＋「17:00」のように、結果が２４時間を超える場合の計算結果はどう表示されるでしょうか。
下の図のように、足し算の結果は「3:00」となりました。
このように、足し算の結果が「0:00」を超える場合は、時間が24時でリセットされて24時間以内で表示されます。
この計算結果を「27：00」というように、２４時間以上で表示したい場合は、下の見出しの方法を利用してください。
足し算の方法(２４時間を超える場合)
足し算の結果を「27:00」や「54:00」といったように、24時間以上で表示する場合は「セルの書式設定」画面から表示方法を変更する必要があります。
表示を変更する手順を紹介します。
手順１．「セルの書式設定」を選択
24時間以上で表示したいセルを選択してから右クリックし、右クリックメニューの「セルの書式設定」をクリックします。
手順２．「ユーザー定義」を選択
「セルの書式設定」画面の「表示形式」タブから「ユーザー定義」を選択します。
手順３．「ユーザー定義」を設定
[種類]の下のユーザー定義の設定枠に、【[ｈ] :ｍｍ】と入力し、「OK」をクリックします。
手順４．表示の変更を確認
選択したセルの表示が「3：00」から「27：00」に変わります。
このようにセルの表示形式を変更することで、合計時間が100時間であっても「100：00」と表示することが可能です。
足し算の方法(セル内で加算)
時間が入力されたセルを参照せずに時間の足し算を行うことも可能です。
下の図のように、数式の中に時間を「”」(ダブルクォーテーション)で囲んで利用すると、足し算に利用することが出来ます。
下の図のように、セルを参照せずに足し算することが出来ます。
SUM関数の使い方
足し算だけでなく、SUM関数で時間を集計することが可能です。
下の図のように、SUM関数を挿入します。「=SUM(B3:B5)」
下の図のように、時間を集計することが出来ます。集計結果が24時間を超える場合は、上の見出しで紹介した方法で、セルの書式設定を変更しましょう。
引き算の方法
時間の引き算も演算記号「-」を使うことで計算が可能です。
引き算の方法(結果がプラスの場合)
開始時刻と終了時刻など勤務時間を管理する際に便利です。
しかし、引き算の結果がマイナスとなる時は、「########」と表示がエラーとなります。
引き算の結果がマイナスでエラーとなる場合は、以下の見出しの対処方法を利用してください。
引き算の方法(結果がマイナスの場合)
時間の引き算の結果がマイナスになりえる場合は、IF関数と併用して数式を作成する必要があります。
数式の作成手順を以下で紹介します。
手順１．引き算を挿入
通常どおり、引き算を挿入します。
=C4-B4
手順２．IF関数を挿入
引き算の後ろにIF関数を挿入し、引き算の結果がマイナスになる場合は「１」を加えます。
=C4-B4+IF(C4<B4,1)
IF関数を使うことで、もし引き算の結果がマイナスであるのであれば「１」、そうでなければ「0」を加算して、セルの値がマイナスになることを防ぐために挿入しています。
どうして「１」の挿入かというと、エクセルは裏で時刻「24:00」を数値「１」として管理しているためです。
Excelでは、日付と時刻は「シリアル値」という値で管理されています。日付は、1900/1/1を基準にした経過日数で表されます。時刻は、24時間を「1」として分数で表します。午前1時なら「1/24」（0.0416666...）、正午12時なら「12/24」（0.5）です。
手順３．引き算の結果を確認
引き算を確定すると、「3:30」から「21:00」の結果が「6:30」と正しく表示されます。
時間を時間数や分数や秒数で表示する方法
足し算や引き算で算出した時間を時間数や分数や秒数に表示を変換する方法を解説します。
「3:45」⇒「3」や「3.75」と時間数で表示
「3:45」⇒「225」と分数で表示
「3:45」⇒「13,500」と秒数で表示
「セルの書式設定」を利用する方法と、数式で参照して変換する方法の２つの方法をそれぞれ紹介します。
その１：「セルの書式設定」を利用する方法
一つ目の方法は、「セルの書式設定」画面で表示方法を変更する方法です。
まず「時間」を「分数」で表示する方法を説明します。
手順１．「セルの書式設定」を選択
分数で表示したいセルを選択してから右クリックし、右クリックメニューの「セルの書式設定」をクリックします。
手順２．「ユーザー定義」を設定
「分類」リストの中から「ユーザー定義」を選択し、[種類]の下のユーザー定義の設定枠に、【[ｍｍ]】と入力し、「OK」をクリックします。
手順３．セルの表示を確認
そうすると、下の図のように、時間を分数で表示することが出来ます。
このように「ユーザー書式設定」画面から表示形式を分であれば[ｍｍ]、秒であれば[ｓｓ]、時間であれば[ｈｈ]に変更すれば時間数、分数、秒数で表示出来ます。
ただし、時間数のように小数点以下は切り捨てられて表示されますので、正確に表示したい場合は下の見出しで紹介する２つ目の方法を利用してください。
その２：数式を挿入する方法
2つ目の方法は、数式を使って時間(h:mm)を分数や秒数や時間数に変換する方法です。
手順１．掛け算を挿入
時間数を表示したいセルに、「＝時間(h:mm)*24」の数式を挿入します。
上の見出しで少し説明したように、日付と時刻は「シリアル値」という値で管理されていて、時刻は、24時間を「1」としてエクセルは管理しているため、24を乗じることで時間数に変換します。
手順２．「セルの書式設定」を選択
セルの表示形式が時刻に自動で変更されてしまうため、計算結果も時間で表記されてしまいます。
この表示を修正するために、右クリックメニューの「セルの書式設定」を選択します。
手順３．「セルの書式設定」を選択
「表示形式」タブの「標準」を選択し、「OK」で画面を閉じます。
手順４．セルの表示を確認
セルの表示形式を変更した結果、下のように「3:45」は「3.75」時間に変更されます。
同様に、分数で表示したい場合は、さらに60を乗じることで分数で表示することが出来ます。
=B3*24*60
秒数で表示したい場合は、さらに60を乗じることで分数で表示することが出来ます。
=B3*24*60*60
秒数や分数を『何日何時間何分』で表示する方法
秒数や分数を以下のように表示方法を変更することも可能です。
- 156,700秒を『何日何時間何分何秒』で表示
- 156,700秒を『何日何時間何分』で表示
- 156,700秒を『何日何時間』で表示
- 156,700秒を『何時間何分』で表示
- 9,100分を『何日何時間何分』で表示
- 9,100分を『何日何時間』で表示
以下の記事で詳しく紹介していますので、合わせて参考にしてください。
