Excelで時間の加減算（時差、労働時間）を計算する

のため日付と時刻は Excel のバックエンドに数値として保存されます, そのため、日付と時刻の値に対して単純な算術演算や数式を簡単に使用できます。

たとえば、XNUMX つの異なる時刻値または日付値を加算および減算したり、指定された XNUMX つの日付/時刻間の時差を計算したりできます。

このチュートリアルでは、その方法を説明します Excel で時間を使用して計算を実行するいくつかの方法（時差の計算、時間の加算と減算、さまざまな形式での時間を表示、時間値の合計の計算など）。

Excelは日付と時刻をどのように処理しますか？

前述したように、日付と時刻はExcelのセルに数値として保存されます。整数はXNUMX日を表し、数値の小数部分はXNUMX日の一部を表します（時間、分、秒の値に変換できます）

たとえば、値1は、Excelで1900年1月1日を表します。これは、Excelが日付の検討を開始するための開始点です。

つまり、2は1990年1月2日を意味し、3は1900年1月3日を意味し、以下同様に、44197は2021年1月1日を意味します。

注：Excel forWindowsとExcelfor Macは、異なる開始日に従います。Excel for Windowsの1は1900年1月1日を意味し、Excel forMacの1は1904年1月1日を意味します

これらの数値に小数点以下の桁がある場合、Excelはそれらの数値をXNUMX日の一部として扱い、時間、分、および秒に変換できます。

たとえば、44197.5は、2021年1月1日の12:00:00 PMを意味します。

したがって、Excelで時間値を操作している場合は、基本的に数値の小数部分を操作しています。

Excelは、小数部分を、時間のみ、分のみ、秒のみ、または時間、分、秒の組み合わせなど、さまざまな形式に変換する柔軟性を提供します。

Excelで時間がどのように保存されるかを理解したので、ExcelでXNUMXつの異なる日付または時刻の時間差を計算する方法の例をいくつか見てみましょう。

XNUMXつの時間の時間差を計算する式

多くの場合、あなたがする必要があるのは、XNUMXつの時間値の間の合計経過時間を見つけることです（たとえば、インタイムとアウトタイムのタイムテーブルの場合）。

選択する方法は、セルに時刻がどのように記載されているか、および結果をどのようにフォーマットするかによって異なります。

いくつかの例を見てみましょう

Excelで時間差を計算するための簡単な減算

時刻はExcelに数値として保存されるため、2つの時刻値の差を見つけるために、終了時刻から開始時刻を簡単に差し引くことができます。

終了時間-開始時間

減算の結果も、XNUMXつの時間値の間の経過時間を表すXNUMX進値になります。

以下は、開始時刻と終了時刻があり、単純な減算によって時間差を計算した例です。

結果が（XNUMX進数または時間/分の値ではなく）時間形式で表示される可能性があります。上記の例では、セルC2の結果は09ではなく30:9.5 AMを示しています。

Excelは隣接する列から書式をコピーしようとするため、これは問題ありません。

小数に変換するには、セルの形式を「一般」に変更します（このオプションは「数値」グループの「ホーム」タブにあります）。

結果を取得したら、さまざまな方法でフォーマットできます。たとえば、値を時間のみ、分のみ、または時間、分、秒の組み合わせで表示できます。

使用できるさまざまな形式は次のとおりです。

書式	何ができるか
hh	XNUMXつの日付の間に経過した時間数のみを表示する
mm	時間を04桁で表示する（例：12またはXNUMX）
hh: mm	10つの日付の間に経過した時間と分を表示します（例：20：XNUMX）
hh：mm：ss	10つの日付の間に経過した時間、分、秒を表示します（例：20：36：XNUMX）

これらのカスタム日付形式をどこでどのように適用するか疑問に思っている場合は、次の手順に従ってください。

1. 日付形式を適用するセルを選択します
2. Controlを押しながら1を押します（Macを使用している場合はCommand + 1）
3. 開いた[セルの書式設定]ダイアログで、[数値]タブをクリックします（まだ選択されていない場合）。
4. 左側のペインで、[カスタマイズ]をクリックします
5. [タイプ]フィールドに任意のフォーマットコードを入力します（この例ではhh：mm：ssを使用しています）
6. [OK]をクリックします

上記の手順でフォーマットが変更され、フォーマットに応じた値が表示されます。

カスタム数値フォーマットはセルの値を変更しないことに注意してください。値の表示方法を変更するだけです。したがって、セルに時間の値のみを表示することを選択できますが、それでも元の値が保持されます。

プロのヒント：合計時間が24時間を超える場合は、代わりに次のカスタム数値形式を使用してください：[hh]：mm：ss

時間差を時間、分、または秒で計算します

時間値を減算すると、Excelは結果の時間差を表すXNUMX進数を返します。

各整数はXNUMX日を表すため、数値の小数部分はXNUMX日のほとんどを表し、時間、分、または秒に簡単に変換できます。

時間差を時間で計算します

以下に示すようなデータセットがあり、XNUMXつの時間値の間の時間数を計算するとします。

次の式は、時間差を時間単位で示します。

=（B2-A2）* 24

上記の式は、XNUMXつの時間値の間で経過した合計時間数を示します。

時々Excelは助けようとし、また時間形式であなたに結果を与えるでしょう（以下に示されています）。

[ホーム]タブをクリックし、[数値]グループの形式として[数値]を選択すると、簡単に数値形式に変換できます。

XNUMX回の間に経過した合計時間数（小数部分なし）を返したい場合は、次の式を使用します。

= INT（（B2-A2）* 24）

注：この式は、XNUMXつの時間値が同じ日にある場合にのみ機能します。日付が変更された場合、この式は間違った結果をもたらします（時間値のXNUMXつは別の日付であり、XNUMX番目は別の日付です）。このチュートリアルの後半で日付が変更された場合は、時差の計算式を紹介するセクションを確認してください。

分単位で時差を計算する

分単位の時間差を計算するには、結果の値に1440日の合計分数（つまり、24または60 * XNUMX）を掛ける必要があります。

以下に示すようなデータセットがあり、開始日から終了日までに経過した合計分数を計算するとします。

これを行うことができる式は次のとおりです。

=（B2-A2）* 24 * 60

時間差を秒単位で計算します

秒単位の時間差を計算するには、結果の値に24日の合計秒数（つまり、60 * 60 * 86400またはXNUMX）を掛ける必要があります。

以下のようなデータセットがあり、開始日から終了日までに経過した合計秒数をカウントするとします。

これを行うことができる式は次のとおりです。

=(B2-A2)*24*60*60

TEXT関数を使用して時間差を計算します

フォーマットの変更を気にせずに時差をすばやく取得するもうXNUMXつの簡単な方法は、TEXT関数を使用することです。

TEXT関数を使用すると、数式で書式を指定できます。

= TEXT（end_date-start_date、format）

最初のパラメーターは実行する計算であり、XNUMX番目のパラメーターは計算結果を表示する形式です。

以下に示すようなデータセットがあり、XNUMXつの時間の時間差を計算するとします。

さまざまな形式で結果を表示する数式を次に示します。

時間のみを表示：

= TEXT（B2-A2、 "hh"）

上記の式は、XNUMXつの時間値の間で経過した時間数の結果のみを示します。結果が9時間30分である場合でも、9のみが表示されます。

合計分を表示

= TEXT（B2-A2、 "[mm]"）

合計秒数を表示

= TEXT（B2-A2、 "[ss]"）

時間と分を表示する

= TEXT（B2-A2、 "[hh]：mm"）

時間、分、秒を表示する

= TEXT（B2-A2、 "hh：mm：ss"）

形式（またはmmと[mm]）のhhと[hh]の違いがわからない場合は、角かっこを使用すると、時間の値であっても、24つの日付の間の合計時間数がわかります。 24より大きい。したがって、XNUMX時間以上異なるXNUMXつの日付値を減算する場合、[hh]を使用すると合計時間数が得られますが、hhは終了日から経過した時間数のみを示します。

他の単位を無視して、XNUMXつの単位（時間/分）で時差を取得します

XNUMXつの時間値の時間差を時間、分、または秒で計算するだけの場合は、専用のHOUR、MINUTE、またはSECOND関数を使用できます。

これらの各関数は、XNUMXつのパラメーター、時間値を取り、指定された時間単位を返します。

以下に示すようなデータセットがあり、これらXNUMXつの時間の間に経過した時間と秒の合計数を計算するとします。

これを行うための式は次のとおりです。

XNUMX回の間に経過した時間数を計算します

= HOUR（B2-A2）

時間値の結果に基づいて分を計算します（完了時間を除く）

= MINUTE（B2-A2）

時間値の結果から秒を計算します（完了した時間と分を除く）

= SECOND（B2-A2）

これらのHOURS、MINUTE、およびSECOND式を使用するときに知っておくべきいくつかのこと：

• 終了時刻と開始時刻の差を負にすることはできません（これは通常、日付が変更された場合に当てはまります）。この場合、これらの数式は#NUM！を返します。間違い
• これらの式は、結果の時間値の時間部分のみを使用します（そして日部分は無視します）。したがって、終了時刻と開始時刻の差が2日、10時間、32分、および44秒の場合、HOUR式は10になり、MINUTE式は32になり、SECOND式は44になります。

現在の経過時間を計算します（開始時刻から）

開始時刻と現在時刻の間の合計経過時間を計算する場合は、終了時刻の代わりにNOW式を使用できます。

NOW関数は、それが使用されているセルの現在の日付と時刻を返します。これは、入力引数を受け入れない関数のXNUMXつです。

したがって、開始時刻と現在時刻の間の合計経過時間を計算する場合は、次の式を使用できます。

= NOW（）-開始時間

以下は、列Aに開始時刻があり、列Bにこれまでの時刻がある例です。

開始日時と現在時刻の時差が24時間を超える場合は、結果をフォーマットして日付と時刻の部分を表示できます。

これは、次のTEXT式を使用して実行できます。

= TEXT（NOW（）-A2、 "dd hh：ss：mm"）

セルのカスタム形式を変更して（このチュートリアルで前述したように）、日付と時刻の部分が表示されるようにすることで、同じことを実現することもできます。

開始時刻に時間部分しかない場合、Excelはそれを1990年1月1日として扱います。

この場合、NOW関数を使用してこれまでの経過時間を計算すると、間違った結果になります（結果の値には、1990年1月1日からの合計経過日数も含まれるため）。

この場合、次の式を使用できます。

= NOW（）-INT（NOW（））-A2

上記の式は、INT関数を使用して、now関数によって返される値から日数の部分を削除します。この値は、時間差の計算に使用されます。

NOWは揮発性の関数であり、シートが変更されると更新されますが、リアルタイムでは更新されないことに注意してください

日付が変更されたときの時刻を計算します（Excelで負の時刻を計算して表示します）

これまでに説明した方法は、終了時刻が開始時刻よりも遅い場合にうまく機能します。

ただし、この問題は、終了時刻が開始時刻よりも短い場合に発生します。これは通常、タイムシートに記入し、日付と時刻全体ではなく、時刻のみを入力した場合に発生します。

この場合、夜勤で日付が変わると、終了時刻が開始時刻より早くなることがあります。

たとえば、午後6時に作業を開始し、午前00時に作業を終了して一時停止したとします。

時間値のみを使用している場合、終了時間から開始時間を差し引くと、9時間（9〜18）の負の値になります。

Excelは負の時間値を処理できません（さらに言えば、タイムトラベルができない限り、人間）

この場合、日付が変わって判断する方法が必要であり、それに応じて計算する必要があります。

ありがたいことに、非常に簡単な回避策があります。

以下のようなデータセットがあり、開始時刻と終了時刻があるとします。

お気づきのように、開始時刻が夕方で、終了時刻が朝である場合があります（これは、夜間のシフトであり、日が変更されたことを示します）。

次の数式を使用して時間差を計算すると、結果が負の値であるセルにポンド記号（下の画像で黄色で強調表示されている）が表示されます。

= B2-A2

これはIF式です。時間差の値が負の場合、負の場合は正しい結果を返します。

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

これはほとんどの場合うまく機能しますが、開始時間と終了時間が24時間以上離れている場合はまだ十分ではありません。たとえば、誰かが1日目の午前9時にログインし、00日目の午前2時にログアウトします。

これは24時間以上であるため、その人が2時間後に辞めるか26時間後に辞めるかを知る方法はありません。

これを修正する最善の方法は、エントリに日付と時刻が含まれていることを確認することですが、作業している時間だけであれば、上記の式でほとんどの問題が解決するはずです（誰もがそれ以上作業する可能性は低いことを考慮して） 24時間）

Excel での時間の加算と減算

これまで、開始時間と終了時間の例を見てきましたが、時差を見つける必要があります。

Excelでは、既存の日付と時刻の値から固定時刻の値を簡単に加算または減算することもできます。

たとえば、キュ​​ーに入れられたタスクのリストがあり、各タスクに指定された時間がかかり、各タスクがいつ終了するかを知りたいとします。

この場合、各タスクにかかる時間を開始時間に簡単に追加して、タスクがいつ完了すると予想されるかを確認できます。

Excelは日付と時刻の値を数値として保存するため、追加しようとしている時刻がExcelがすでに従う形式であることを確認する必要があります。

たとえば、Excelの日付に1を追加すると、次の日付が表示されます。これは、Excelでは1が24日（XNUMX時間に相当）を表すためです。

したがって、既存の時間値に1時間を追加する場合は、先に進んで1を追加することはできません。時間の値を、時間を表す小数部分に変換する必要があります。分と秒の追加についても同じことが言えます。

時間関数を使用する

Excelの時間関数は、時、分、秒の値を取り、それらを時間を表すXNUMX進数に変換します。

たとえば、既存の時間に4時間を追加したい場合は、次の式を使用できます。

=開始時間+ TIME（4,0,0）

これは、既存の時刻に追加する時間、分、秒がわかっていて、時刻をXNUMX進値に正しく変換することを心配せずに、TIME関数を使用できる場合に便利です。

また、TIME関数は、入力した時間、分、秒の値の整数部分のみを考慮することに注意してください。たとえば、TIME関数で5.5時間を使用すると、5時間が追加され、小数部分は無視されます。

また、TIME関数は24時間未満の値のみを追加できることに注意してください。これにより、時間の値が24を超えると、誤った結果が得られます。

同じことが分と秒の部分にも当てはまり、関数は60分と60秒未満の値のみを考慮します

TIME関数を使用して時間を加算したのと同じように、時間を減算することもできます。上記の数式の+記号をマイナス記号に変更するだけです

基本的な算術を使用する

時間関数は使いやすいですが、いくつかの制限があります（前述のとおり）。

より詳細な制御が必要な場合は、ここで説明する算術法を使用できます。

概念は単純です。時間値をその日の一部を表すXNUMX進値に変換し、Excelの任意の時間値に追加できます。

たとえば、既存の時間値に24時間を追加する場合は、次の式を使用できます。

=開始時間+24/24

これは、既存の時間値にXNUMX日を追加していることを意味します。

同じ概念を使用して、時間値に30時間を追加するとします。次の式を使用できます。

=開始時間+30/24

上記の式は同じことを行います。ここで、（30/24）の整数部分は追加する時間の合計日数を表し、小数部分は時間/分/秒を表します。

同様に、特定の分数を時間値に追加する場合は、次の式を使用できます。

=開始時間 +（分を追加）/ 24 * 60

追加する秒数がある場合は、次の式を使用できます。

=開始時間+（追加の分）/ 24 * 60 * 60

この方法は時間関数を使用するほど単純ではありませんが、すべての場合に機能し、同じ概念に従うため、はるかに優れていると思います。時間関数とは異なり、24時間未満または24時間以上の時間を追加することを心配する必要はありません

時間を差し引いても同じ概念に従うことができます。上記の式で+を負の符号に変更するだけです

Excelで時間を合計する方法

Excelですべての時間値をすばやく追加したい場合があります。Excelで複数の時間値を追加するのは簡単です（単純なSUM式だけです）

ただし、Excelで時間を追加する場合は、いくつかのこと、特に結果を表示するセル形式について知っておく必要があります。

例を見てみましょう。

以下に、列Bのタスクのリストと各タスクにかかる時間を示します。これらの時間をすばやく追加して、これらすべてのタスクにかかる合計時間を知りたいと思います。

セルB9では、単純なSUM数式を使用して、これらすべてのタスクにかかる合計時間を計算しました。これにより、18：30の値が得られます（つまり、これらすべてのタスクを完了するのに18時間20分かかります）。

ここまでは順調ですね！

Excelで24時間以上合計する方法

ここで、タスク2の完了時間を1時間から10時間に変更するとどうなるかを見てみましょう。

結果は03:20を示しています。これは、これらすべてのタスクを完了するのに3時間20分かかることを意味します。

これは正しくありません（明らかに）

ここでの問題は、Excelが台無しになっていることではありません。ここでの問題は、結果の値の時間部分のみを表示するようにセルがフォーマットされていることです。

また、ここでの結果の値は24時間を超えるため、Excelは24時間の部分をXNUMX日に変換し、ユーザーに表示される値から削除して、残りの時間、分、秒のみを表示することにします。

幸い、これは簡単に修正できます。

セル形式を変更して、24時間を超えても時間を表示するように強制するだけです。

使用できる形式は次のとおりです。

書式	推測される結果
[うーん	28:30
[m]：ss	1710:00
d「D」hh：mm	04日目30:XNUMX
d「D」hh「最小」ss「秒」	1日04分00秒
d「日」hh「分」ss「秒」	1日04分00秒

[セルの書式設定]ダイアログに移動してカスタム書式を適用することで書式を変更できます。または、TEXT関数を使用して、数式自体で上記の書式のいずれかを使用できます。

次のTEXT式を使用して、24時間を超えている場合でも時間を表示できます。

= TEXT（SUM（B2：B7）、 "[h]：mm：ss"）

または、24時間以上の時間を数日に変換する場合は、次の式を使用します。

= TEXT（SUM（B2：B7）、 "dd hh：mm：ss"）

結果には、日付/時刻ではなくハッシュ（###）が表示されます（原因+修正）

場合によっては、Excelが時間値を表示する代わりに、セルにポンド記号を表示することがあります。

考えられる原因とその修正方法は次のとおりです。

列の幅が十分でない

セルに完全な日付を表示するのに十分なスペースがない場合、ポンド記号が表示されることがあります。

簡単に修正できます。列の幅を変更して幅を広くします。

負の日付値

Excelの日付または時刻の値を負にすることはできません。時間差を計算していて、結果が負の場合、Excelはハッシュ記号を表示します。

正しい結果が得られるように数式を変更する方法を修正しました。たとえば、XNUMX回の時間差を計算していて、日付が変更された場合は、それを考慮して数式を調整する必要があります。

その他の場合は、ABS関数を使用して、負の時間値を正の数に変換して適切に表示することができます。または、IF式を使用して、結果が負であるかどうかを確認し、より意味のある値を返すことができます。

このチュートリアルでは、Excelでの時間の計算に関するトピックを取り上げました（時間差の計算、時間の加算と減算、さまざまな形式での時間の表示、および時間値の合計が可能です）

このチュートリアルがお役に立てば幸いです。

Excel での時間の加算と減算の計算に関するよくある質問