Excelでは時間の計算ができますが、慣れるためには少しコツがいります。
時間の「hh:mm:ss」という表記のまま足し算、引き算ができたり、「〇〇時間」「〇〇分」という表記に変更できたり何かと便利です。
この記事ではそのな時間や期間を正しく効率的に計算する方法を、ExcelCampの講師が解説しています。
ExcelCamp講師 野沢先生Excel初心者向けに、わかりやすく丁寧に解説しています。
まず知っておきたい「時間データ」と「数値データ」の関係
Excelでは、「時間データ」は「数値データ」として扱われます。
1900年1月1日を「1」と定義して1日経つごとに連番を振って管理しているので日付のデータも数値データです。
ExcelCamp講師 野沢先生例えば2022年1月1日には「44562」という数値が割り振られており、1900年1月1日から「44562日目」という意味になります。
「1日(24時間)=1」というルールなので、時間は0~1の間の小数点で表記されます。
具体的には以下の通りになります。
| 時間 | 数値 | 書き方 |
|---|---|---|
| 1日(24時間) | 1 | 1 |
| 半日(12時間) | 0.5 | 1/2 |
| 8時間 | 0.333… | 1/3 |
| 1時間 | 0.04166… | 1/24 |
| 1分 | 0.0006944… | 1/24/60 |
| 1秒 | 0.00001157… | 1/24/60/60 |
ExcelCamp講師 野沢先生なので例えば、
2022年1月1日に「1」を足せば翌日の日付になりますし、「1/2」を足せば半日後の時間に、「1/24/60」を足せば1分後の時間になります。
時間計算をラクにする「TIME関数」
しかし、上記のようにいちいち時間を「1/2」「1/24/60」と書いて出すのは面倒です。
ExcelCamp講師 野沢先生それに「18分後」「6時間26分後」など、すぐに計算式が出せないことのほうが多そうですよね。
そこで便利なのがTIME関数です。直感的に「何時何分何秒後」の時間計算ができ、瞬時に結果も表示してくれます。
TIME関数の書き方
=TIME(時,分,秒)
例えば現在表示している時刻に「18分」を足したい場合、以下のように書きます。
ExcelCamp講師 野沢先生18分は「0時間18分0秒」なので、
TIME(0,18,0)と書けば18分という意味になります。
正しく「18分」が足されたことがわかります。
TIME関数を使うと、上で説明した時間の書き方がとても単純になります。
| 時間 | 書き方(直接入力) | 書き方(TIME関数) |
|---|---|---|
| 1日(24時間) | 1 | – |
| 半日(12時間) | 1/2 | =TIME(12,0,0) |
| 8時間 | 1/3 | =TIME(8,0,0) |
| 1時間 | 1/24 | =TIME(1,0,0) |
| 1分 | 1/24/60 | =TIME(0,1,0) |
| 1秒 | 1/24/60/60 | =TIME(0,0,1) |
ExcelCamp講師 野沢先生「半日」「8時間」「30分」など足したい時間が決まっている場合はTIME関数を使った書き方がおすすめです！
経過時間を計算する方法
例えば出勤時間と退勤時間から、経過時間(勤務時間)を計算するとします。
上記で説明した通り時間は「数値データ」なので、まずは普通に引き算を書いてみます。
すると結果は次のように表示されます。
たしかに計算は合っていますが、「9:00 AM」ではなく「9(時間)」と表示したいですよね。
そんなときに便利な時間表記を「時」「分」「秒」単位に変換する方法をご紹介します。
計算結果を「時」「分」「秒」単位に変換する
「9:00 AM」を「時」「分」「秒」に直すには、以下の手順で操作を行います。
手順1：表示形式を「標準」に戻す
表示形式を標準に戻す：Ctrl&Shift&^
まず、「9:00 AM」は表示形式が時間表記になってしまっているため、標準の数値表記に戻す必要があります。
「セルの書式設定」の下記の場所から直すことができます。
ExcelCamp講師 野沢先生セルの書式設定はCtrl&1で開くことができ、ボックス内の操作もすべてショートカットで行うことが可能です。(関連記事参照)
ExcelCamp講師 野沢先生ただし今回は一瞬で「標準に戻す」操作ができるショートカットがあるので、そちらを覚えたほうが圧倒的に便利です。
ショートカットの手順は、まず表示形式を変更したいセルを選択し、Ctrlキー、Shiftキーを同時に押したまま^キーを押します。
※^キーは数字の0キーの2つ右隣にあります。
手順2：「時間」に直すには24をかける
「0.375」という数字は、単位が「日」です。
そのため*24をすることで単位を「時間」に変更することができます。
Enterキーを押して数式を確定させると、一度表記が元に戻ってしまいます。
すかさずCtrl&Shift&^で表記を標準に戻せば、無事「9(時間)」が表示されます。
ExcelCamp講師 野沢先生毎回「セルの書式設定」ボックスを開いて標準に戻すのは流石に面倒なので、Ctrl&Shift&^を必ず覚えてください。
手順3：「分」に直すには24*60をかける
同じ要領で、単位を「分」に直すには*24*60をすればうまくいきます(24と60をかける)。
Enterキーで確定すると例によって一度表記がAMに戻るので、Ctrl&Shift&^で表記を標準に戻します。
540分(=9時間)が正しく表示されました。
手順4：「秒」に直すには24*60*60をかける
単位を「秒」にするには、さらに*60をします(24と60と60をかける)。
Ctrl&Shift&^で表記を標準に戻せば、正しい数値である32400秒(=9時間)が表示されました。
ExcelCamp講師 野沢先生毎回計算のたびに表記がAM/PMに戻ってしまうのが厄介ですが、ショートカットさえ覚えてしまえばほとんどストレス無く操作が可能ですよ。
合計時間を計算する方法
「時間」「分」「秒」単位に計算結果を変換する方法さえ知っていれば、合計時間を出す方法は簡単です。
SUM関数を使って簡単に計算することができます。
時間が「24時間以上」になるときの計算方法
時間はあくまで0~1(24時間)の間で定義されている概念なので、24時間を超えてしまうとうまく表示できなくなります。
例えば「26時間」を、無理やり26:00:00と書いてみます。
Enterキーを押すと、結果は勝手に「2:00:00」と表記されてしまいます。
ExcelCamp講師 野沢先生24時間以上の時間を正しく計算するには、「セルの書式設定」から独自に表記ルールを定義する必要があります。
まず24時間以上表示したいセルを選択し、Ctrlキーを押しながら数字の1キーを押し「セルの書式設定」ボックスを開きます。
上のタブが「表示形式」の場所にいない場合は、←キーと→キーでタブを移動させます。
「分類(C)」の中の「ユーザー定義」を選びたいので、Altキーを押しながらCキーを押して「分類(C)」内にアクティブを移動させます。
↓キーを押して「ユーザー定義」までカーソルを移動させてください。
次に種類(T)にアクティブを移したいので、Altキーを押しながらTキーを押します。
すると入力欄にアクティブが移ります。
ExcelCamp講師 野沢先生ここの表記は特殊なので一応解説します。
h：時(hour)
m：分(minute)
s：秒(second)
と、それぞれアルファベットが対応しているので、h:mm:ssは「時間を『0:00:00』と表記しますよ」という意味になります。
24時間以上の表記をするには、入力欄の中の「h」を、「[h]」に書き換えてください。
「サンプル」と書かれたプレビュー画面に「26:00:00」と書かれていれば成功です。
Enterキーで表示形式を確定させると、無事セルにも「26:00:00」が表示されるようになりました。
ExcelCamp講師 野沢先生計算上、時間が24時間を超えてしまうことはよくあるので、「[]でhを囲えばうまくいく」と覚えておくと便利です。
計算結果がマイナスになるときの表示方法
例えば先月と今月の稼働時間の差を計算したいとします。
このように引き算を書いてEnterキーを押すと、「#######」という表記が出てしまいます。
ExcelCamp講師 野沢先生今回は計算結果がマイナスになってしまいます。
時間はExcel上で「0~1」の間の数字で定義されているため、0より小さいマイナスの数値ではエラーが出てしまうのです。
エラーを出さずに「-4:00:00」と表示させるには以下の手順が必要です。
「マイナス」の時間を表示させる方法
手順①：ABS関数で計算結果を絶対値に直す
手順②：IF関数で0以下の時間に「-(マイナス符号)」をつける
手順③：TEXT関数で数字データを「文字列」に変更する
手順1：ABS関数で計算結果を絶対値に直す
まず、時間がマイナスのままでは「#####」の表記が直らないため、マイナスを取って計算ができる状態にします。
マイナスを取るにはABS関数が便利で、以下のように書きます。
ExcelCamp講師 野沢先生マイナスの数字から-(マイナス符号)を取った数字のことを「絶対値」と呼びます。
※「ABS関数」は絶対値(Absolute value)の頭文字が由来
「絶対参照」とは全く別の話ですので、混同しないようご注意ください。
Enterキーを押すと、-(マイナス符号)が取れて「4:00:00」と表示されるようになりました。
これで時間データ(数値データ)として扱えるようになりました。
手順2：IF関数で0以下のときに「-」をつける
次はIF関数を使って、以下のような式を書きます。
IF関数の書き方
=IF(論理式,真の場合,偽の場合)
=IF(C4-B4<0,”-h:mm:ss”,”h:mm:ss”)
論理式：C4-B4<0(もし差がマイナスだったら)
真の場合：“-h:mm:ss”(表示形式にマイナスを付けてね)
偽の場合：“h:mm:ss”(表示形式に何も付けないでね)
ExcelCamp講師 野沢先生ここで書くIF関数は、手順3のTEXT関数の表示形式を条件分岐させるために使います。
そもそもIF関数の使い方がわからないという方は、以下の関連記事をご確認ください。
手順3：TEXT関数で数値データを「文字列」に変更する
TEXT関数は、「数値データ」の表示形式を自由に変更できる関数です。
TEXT関数の書き方
=TEXT(値,表示形式)
=TEXT(ABS(C4-B4),IF(C4-B4<0,”-h:mm:ss”,”h:mm:ss”))
値：ABS(C4-B4)(差の絶対値)
※値は「数値データ」でなければいけない
表示形式：IF(C4-B4<0,”-h:mm:ss”,”h:mm:ss”)(手順2のIF文)
実際に書いてみると、無事「#######」というエラーが出ずに「-4:00:00」と表示されていることがわかります。
ExcelCamp講師 野沢先生手順1のABS関数は、値を「エラー」から「数値データ」に変換することでTEXT関数を使える状態にするために書きました。
また手順2のIF関数は、TEXT関数の表示形式を「マイナスの場合」と「そうでない場合」で条件分岐させるために書いています。
時間データから「○時」「○分」「○秒」だけを取り出す関数
最後にhh:mm:ssと書かれた時間のデータから、「時」「分」「秒」だけを取り出したり、「時:分」「分:秒」だけを抜き出す方法をご紹介します。
ExcelCamp講師 野沢先生それぞれ専用の関数が存在するので、使う頻度が多い方は是非覚えてください。
「時」だけを取り出す：HOUR関数
HOUR関数の書き方
=HOUR(時間データ)
時間データから「時」の値だけを取り出すにはHOUR関数を使います。
書き方は簡単で、()内に時間データを入れるだけです。Enterキーを押して関数を確定させれば、「15時」だけを抜き出すことができます。
「分」だけを取り出す：MINUTE関数
MINUTE関数の書き方
=MINUTE(時間データ)
時間データから「分」の値だけを取り出すにはMINUTE関数を使います。
書き方はHOUR関数同様で、()内に時間データを入れるだけです。
「秒」だけを取り出す：SECOND関数
SECOND関数の書き方
=SECOND(時間データ)
時間データから「秒」の値だけを取り出すにはSECOND関数を使います。
これも書き方はHOUR関数、MINUTE関数同様です。
ExcelCamp講師 野沢先生取り出したい要素の名前が、そのまま関数名になっているので覚えやすいですね！
ちなみに日付データの要素を取り出す関数は以下のとおりです。
「年」を取り出す：YEAR関数
「月」を取り出す：MONTH関数
「日」を取り出す：DAY関数
「時:分」「分:秒」を抜き出す：TEXT関数
TEXT関数の書き方
「時:分」を抜き出す：=TEXT(時間データ,”hh:mm”)
「分:秒」を抜き出す：=TEXT(時間データ,”mm:ss”)
時:分:秒から、時:分、分:秒など一部のデータを抜き出すには、TEXT関数で表示形式を変更することでうまくいきます。
休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修
私たちが提供するExcel研修「上位1%を目指す！ExcelCamp」では、これまで延10,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。
※10点満点中の評価の平均値。
休日1日を研修に使うのは「やや重たい」というご意見もいただきます。
しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです。
実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。
そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。
参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。
動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中！
※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。
現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。
全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。
著書「神速Excel」も絶賛発売中
