リテラアップダウンロード

Excelの時間計算|経過時間や合計時間、終了時間を求める方法

Excelの時間計算

Excel(エクセル)でシフト表やタイムカードを作る時に便利なのが時間計算です。時間は12進法、分と秒は60進法なので、一見厄介ですが、Excelは時刻にも対応しており、経過時間や合計時間などを計算して求めることができます。

Excelで経過時間を計算する方法

Excel(エクセル)で経過時間を計算する方法を紹介します。

経過時間とは、終了時刻から開始時刻を引いたものです。

Excelでは、演算子「-」(マイナス)を使って引き算することで、経過時間を求めることができます。

Excelで「:」(コロン)で区切られた値を入力すると、自動的に「9:00」のように時刻表示に変わります。

Excelで経過時間を計算する方法

出勤(開始時間)と退勤(終了時間)が入力された勤務表を例に、勤務時間(経過時間)を計算してみましょう。

STEP.1
経過時間を表示したいセルに「=」を入力します。

Excelで経過時間を計算する方法

STEP.2
終了時間のセルを選択してから、「-」を入力します。

Excelで経過時間を計算する方法

STEP.3
開始時間のセルを選択します。

Excelで経過時間を計算する方法

STEP.4
「Enter」キーを押すと経過時間が計算されます。

Excelで経過時間を計算する方法

  • 経過時間を「時」で計算・表示する方法
  • 経過時間を「分」で計算・表示する方法
  • 経過時間を「秒」で計算・表示する方法

経過時間を「時」で計算・表示する方法

4:30は「時」に直すと4.5時間です。

では、Excel(エクセル)上の経過時間を「時」に直すにはどうすればよいでしょうか。

「時」で表示するには、「4:30」に24をかけます。

STEP.1
「時」に直した結果を表示させるセルに「=B3*24」と入力します。

経過時間を「時」で計算・表示する方法

STEP.2
「Enter」キーを押すと計算結果が表示されます。

経過時間を「時」で計算・表示する方法

STEP.3
計算結果のセルを右クリックして「セルの書式設定」の「表示形式」タブで「標準」に設定します。

経過時間を「時」で計算・表示する方法

STEP.4
「時」の表示に変わりました。

経過時間を「時」で計算・表示する方法

経過時間を「分」で計算・表示する方法

では、4:30を「分」に直すにはどうするのでしょうか。

前の項で「時」に直すことはできました。1時間は60分ですので、「分」に直すには、さらに60をかけます。

「=B3*24*60」と入力してEnterキーで確定し、書式を「標準」に直すと「分」で表示されました。

経過時間を「分」で計算・表示する方法

経過時間を「秒」で計算・表示する方法

続いて、「秒」まで出してみましょう。

1分は60秒ですから、「分」の計算結果にさらに60をかけます。

「=B3*24*60*60」と入力してEnterキーで確定し、書式を「標準」に直すと「秒」で表示されました。

経過時間を「秒」で計算・表示する方法

Excelで合計時間を計算する方法

Excel(エクセル)で合計時間を計算する場合は、演算子「+」を使って足します。

午前中に3時間、午後に5時間勤務した場合の合計時間を出してみましょう。

式は「=B3+C3」となり、合計時間が計算されました。

Excelで合計時間を計算する方法

合計時間が24時間以上で計算が合わない場合の原因・対応方法

Excel(エクセル)で1週間の勤務時間の合計を出してみましたが、計算が合いません。

よく見ると、25:00→1:00と変換されているようです。時刻を24時間表示にする時と同じく、24時が「0時」と判定されているように見えます。

24時間を超えた場合に合計時間を表示させたい場合は、セルの書式設定で「[h]:mm」と設定します。

STEP.1
合計時間のセルを右クリックして、「セルの書式設定」を開きます。

合計時間が24時間以上で計算が合わない場合の原因・対応方法

STEP.2
「表示形式」タブで「ユーザー定義」を選択し、「種類」の欄に「[h]:mm」と入力して「OK」ボタンをクリックします。

合計時間が24時間以上で計算が合わない場合の原因・対応方法

STEP.3
合計時間が正しく表示されました。

合計時間が24時間以上で計算が合わない場合の原因・対応方法

Excelで終了時間を計算する方法

Excel(エクセル)で終了時間を計算する方法です。

終了時間は開始時間に経過時間を足したものなので、演算子「+」を使って足し算します。

出勤(開始時間)と勤務時間(経過時間)が分かっている例で、退勤時間(終了時間)を計算してみましょう。

出勤時間に勤務時間を足して計算します。

STEP.1
終了時間を表示したいセルに「=」を入力します。

Excelで終了時間を計算する方法

STEP.2
開始時間のセルを選択してから、「+」を入力します。

Excelで終了時間を計算する方法

STEP.3
経過時間のセルを選択します。

Excelで終了時間を計算する方法

STEP.4
「Enter」キーを押すと計算結果が表示されます。

Excelで終了時間を計算する方法

  • 終了時間を「時」で計算・表示する方法
  • 終了時間を「分」で計算・表示する方法
  • 終了時間を「秒」で計算・表示する方法

終了時間を「時」で計算・表示する方法

終了時間が今から「〇時間後」といった形で分かっている場合には、TIME関数での計算が便利です。

TIME関数の構文:TIME(時,分,秒)

例えば、B3セルの時刻の「4時間後」の場合は、「時」の部分に「4」を入れて、「=B3+TIME(4,0,0)」となります。

終了時間を「時」で計算・表示する方法

終了時間を「分」で計算・表示する方法

「〇分後」を求めるには、TIME関数の「分」の部分に入れます。

「試験時間90分」「授業1コマ120分」など、経過時間が分で表されている場合でも、頭の中で変換せずにTIME関数に当てはめればそのまま計算できます。

B3セルの時刻の「90分後」の場合は、「分」の部分に「90」を入れて、「=B3+TIME(0,90,0)」となります。

終了時間を「分」で計算・表示する方法

終了時間を「秒」で計算・表示する方法

競技のタイムなど、秒まで計算が必要な場合もTIME関数で計算できます。

経過時間が「5分20秒」の場合は、TIME関数の「分」と「秒」にそれぞれ「5」と「20」を入れます。

B3セルの時刻の「5分20秒」の場合は、「=B3+TIME(0,5,20)」となります。

終了時間を「秒」で計算・表示する方法

Excelで勤務表の勤務時間計算・給与計算する方法

Excel(エクセル)を使って、勤務表の時間計算や給与計算をしてみましょう。実践に活かせる具体例を使って解説していきますので、自身の職場にも当てはめながらオリジナルのテンプレートを作成してみてください。

  • 1. 勤務時間を計算する
  • 2. 勤務時間の合計を計算する
  • 3. 時刻表示を数値表示に変換する
  • 4. 給与(時給)を計算する

1. 勤務時間を計算する

まずは、勤務表に記載されている1日の勤務時間を計算してみます。その日の勤務時間を表示したいセルに「=(退勤時刻)-(出勤時刻)-(休憩時間)」と入力すればOKです。

例のような勤務表では、先頭セルは「=G10-F10-H10」となります。残りのセルもオートフィルで入力しておきましょう。I10セルの右下にカーソルを移動させ、そのままドラッグドロップしてください。

勤務時間を計算する

2. 勤務時間の合計を計算する

次に1ヶ月の勤務時間の合計を求めてみましょう。複数セルの合計を計算するにはSUM関数を使います。

合計勤務時間を表示したいセルに「=SUM(合計したいセル範囲)」と入力します。例の場合は「=SUM(I10:I39)」となり、これでI10からI39の1ヶ月分の勤務時間が合計されます。合計時間は時刻表示と表示されています。

勤務時間の合計を計算する

3. 時刻表示を数値表示に変換する

時刻表示のままではうまく給与を計算できないため、数値表示に変換しましょう。合計勤務時間が入力されているセルを選択して、「ホーム」タブの「数値の書式」で「数値」を選んでください。

時刻表示を数値表示に変換する

Excel(エクセル)では時刻をシリアル値(0から始まって24時間で「1」となる)で管理しています。時刻データを数値に変換するためにはシリアル値に24を掛ければOKなので、先ほど計算した合計勤務時間に24を掛けておきましょう。必要に応じて小数点以下の処理を行なってください。

時刻表示を数値表示に変換する

4. 給与(時給)を計算する

最後に、数値表示された時間×時給を計算して給与を求めてみましょう。予め時給を所定の欄に入力しておきます。そしてその月の全体給与を表示したいセルに「=合計勤務時間(数値表示したもの)×時給」と入力します。例では「=B7*I7」となります。これで勤怠表を用いて任意の月の給与が計算されます。

給与(時給)を計算する

Excelの時間計算の結果がマイナスの場合について

Excel(エクセル)の時間計算の結果がマイナスになってしまうことがあります。

なぜマイナスになるのか、その理由を理解するには、Excelの時間表示の仕組みを知る必要があります。

  • 時間計算の結果がマイナスの時に「#######」と表示される原因
  • 時間計算の結果がマイナスの時に正しく表示する方法

時間計算の結果がマイナスの時に「#######」と表示される原因

前回タイムとの差を求めるため、今回のタイムから前回のタイムを引いたら「#######」と表示されてしまいました。

時間計算の結果がマイナスの時に「#######」と表示される原因

今回のタイムの方が速いので、「#######」がマイナスの値になっていることは分かりますが、セル幅を広げても表示されません。

Excel内での時刻は、時刻形式の表示になっていても、内部で「シリアル値」という値で管理されています。時刻のシリアル値は24時間を「1」として数値で表したものです。

内部ではシリアル値できちんと計算が行われているのですが、時刻表示ではマイナスの値を表示してくれないのです。

時間計算の結果がマイナスの時に正しく表示する方法

時刻表示のままではマイナス表示ができないことが分かったら、文字列に変換すれば解決しそうです。

数値を文字列に変換する「TEXT関数」、絶対値を表示する「ABS関数」、条件を設定する「IF関数」の3つを組み合わせると、マイナス表示の時刻も表示することができます。

①B3-C3の差を絶対値にする:ABS(B3-C3)

②B3-C3が0以下だった場合、表示に「-」を付ける:IF(B3-C3<0,”-h:mm:ss”,”h:mm:ss”)

③全体を文字列にする:TEXT

=TEXT(ABS(B3-C3),IF(B3-C3<0,”-h:mm:ss”,”h:mm:ss”))

これで、時間計算の結果をマイナス表示で正しく表示できました。

時間計算の結果がマイナスの時に正しく表示する方法

Excelの時間計算について改めておさらい

勤務時間やタイムの記録は、手で計算しなくてもExcel(エクセル)上で簡単に計算できます。さまざまな場面で使えるExcelの時間計算をぜひ活用してみてください。

リテラアップアプリのご利用について、法人の方は下記フォームよりお問い合わせください。

(Excel,WordなどOffice製品やPC関連の技術的な質問にはお答えできかねます。ご了承ご了承ください。)