【Excel】時刻を24時超えで表示する方法!Excelの勤怠表で25:00と正しく計算する書式設定

Excelで勤怠表を作成する際、残業時間などで24時を超える時刻が発生することがあります。しかし、Excelの標準機能では24時を超える時刻を正しく表示・計算できない場合があります。例えば、25:00と入力しても25:00と表示されず、1:00と表示されてしまうことがあります。この記事では、Excelで24時を超える時刻を正しく表示・計算するための書式設定の方法を解説します。勤怠表で正確な残業時間を把握できるようになります。

Excelの時刻表示は、デフォルトでは24時間形式に基づいています。そのため、24時を超えると日付が変わったと認識され、表示がリセットされてしまうのです。この問題を解決するには、ユーザー定義の書式設定を利用する必要があります。これにより、Excelに24時を超える時刻を特殊な形式として認識させ、意図した通りに表示できるようになります。

Excelが24時を超える時刻を正しく扱えない理由

Excelでは、日付と時刻は内部的にシリアル値として扱われています。日付は1900年1月1日を「1」として、日数で表されます。時刻は、1日を「1」として、時間の割合で表されます。例えば、正午(12:00)は「0.5」、午後6時(18:00)は「0.75」となります。

このシリアル値の仕組みにより、Excelは標準で時刻を24時間形式で解釈します。そのため、24時(0時)を超えると、それは次の日の午前0時として扱われるため、表示上は1:00、2:00のようにリセットされてしまうのです。勤怠管理で残業時間などが24時間を超える場合、この標準の表示形式では正確な時間を把握することが困難になります。

24時を超える時刻を表示・計算する書式設定の手順

Excelで24時を超える時刻を正しく表示するには、セルの書式設定でユーザー定義の書式コードを使用します。この設定を行うことで、Excelは時刻を単なる1日のサイクルとしてではなく、経過時間として計算・表示するようになります。

1. 時刻を表示したいセルを選択する
   24時を超える時刻を表示したいセル、またはその範囲をドラッグして選択します。複数セルを選択することも可能です。
2. セルの書式設定を開く
   選択したセルを右クリックし、表示されるメニューから「セルの書式設定」を選択します。または、Excelのリボンメニューの「ホーム」タブにある「数値」グループの右下にある小さな矢印をクリックしても開けます。
3. 「表示形式」タブを選択する
   「セルの書式設定」ダイアログボックスが表示されたら、「表示形式」タブを選択します。
4. 「ユーザー定義」を選択する
   「表示形式」タブの「分類」リストから「ユーザー定義」を選択します。
5. 書式コードを入力する
   「種類」ボックスに、既存の書式コードが表示されている場合はそれを削除し、以下の書式コードを入力します。
   [h]:mm
   この「[h]」が、24時間制限を解除し、合計時間を表示するための重要な部分です。「:mm」は分を表示するための標準的な指定です。
6. 「OK」をクリックする
   設定が完了したら、「OK」ボタンをクリックしてダイアログボックスを閉じます。

この設定を行った後、セルに例えば「25:30」と入力すると、正しく「25:30」と表示されるようになります。また、数式で計算した結果が24時を超える場合も、この書式設定が適用されていれば、正しい合計時間が表示されます。

勤怠表での具体的な計算例

勤怠表で残業時間を計算する際、この書式設定は非常に役立ちます。具体的な計算例を見てみましょう。

例:始業時刻と終業時刻から残業時間を計算する

以下の表のように、A列に始業時刻、B列に終業時刻が入力されているとします。C列に残業時間を計算する数式を入力します。

前提条件:

• A2セル:始業時刻(例: 9:00)
• B2セル:終業時刻(例: 18:30)
• C2セル:残業時間を表示するセル

手順:

1. C2セルに数式を入力する
   C2セルに以下の数式を入力します。
   =B2-A2
   この数式は、終業時刻から始業時刻を引くことで、勤務時間を計算します。
2. C2セルに書式設定を適用する
   C2セル(または残業時間を表示したいセル範囲)を選択し、前述した「24時を超える時刻を表示・計算する書式設定の手順」に従って、書式を[h]:mmに設定します。
3. 数式をコピーする
   C2セルの右下隅にあるフィルハンドルをダブルクリックするか、下にドラッグして、他の行にも数式をコピーします。

例えば、始業時刻が「9:00」、終業時刻が「2:00」の場合、通常は「-7:00」または「17:00」のように表示されてしまいます。しかし、上記の書式設定を適用したセルで「=B2-A2」と計算すると、「17:00」と正しく表示されます。これは、ExcelがB2の「2:00」を翌日の2時と解釈し、A2の「9:00」から引いた結果、17時間経過していると計算するためです。

例:総労働時間や総残業時間を計算する

日々の労働時間や残業時間を計算した後、それらを合計して月間の総労働時間や総残業時間を算出する場合があります。その際も、合計セルに[h]:mmの書式設定を適用することが重要です。

例えば、C2からC31までのセルに日々の残業時間が入力されており、C32セルに合計を表示する場合、C32セルに以下の数式を入力します。

=SUM(C2:C31)

そして、C32セルにも同様に[h]:mmの書式設定を適用します。これにより、日々の残業時間の合計が24時間を超えた場合でも、正しく合計時間が表示されます。

書式設定を適用する際の注意点

24時を超える時刻を表示するための書式設定は非常に便利ですが、いくつか注意すべき点があります。これらの点に留意することで、意図しない結果を防ぎ、正確な勤怠管理を行うことができます。

時刻の入力形式に注意する

書式設定は、あくまで表示形式を変更するものです。Excelが時刻として正しく認識できる形式で入力されていないと、期待通りの計算や表示は行われません。例えば、「25時30分」を「25:30」と入力する必要があります。「25時間30分」や「25.5」といった形式では、Excelはそれを時刻として認識しない可能性があります。

日付の混同に注意する

24時を超える時刻表示は、あくまで「経過時間」として表示しています。もし、始業時刻と終業時刻が日付をまたぐ場合(例:深夜0時を過ぎて翌日の勤務になる場合)は、単純な引き算では正確な勤務時間を計算できません。その場合は、日付と時刻の両方を考慮した数式や、DATE関数、TIME関数などを組み合わせる必要があります。

例えば、終業時刻が翌日の日付になる場合、終業時刻のセルに日付情報も含まれていることを確認してください。もし日付情報がない場合は、Excelは終業時刻をその日のものと解釈し、計算がおかしくなることがあります。その際は、終業時刻のセルにも日付情報を含めて入力するか、数式で日付を考慮した計算を行う必要があります。

Excelのバージョンによる違い

今回紹介した[h]:mmという書式コードは、Excelのほとんどのバージョンで利用可能です。Excel 2019、Excel 2021、Microsoft 365のいずれでも問題なく使用できます。古いバージョン(Excel 2016以前)でも同様に利用できるはずですが、もし古いバージョンでうまくいかない場合は、Excelのヘルプやサポート情報を参照してください。

数値として扱われる場合

書式設定は表示上の問題であり、Excel内部では数値として扱われています。そのため、24時を超える時刻を計算した結果を、さらに別の計算(例:時給計算)に利用する場合、この[h]:mm書式設定が適用されたセルをそのまま参照すれば、Excelは自動的に経過時間として計算してくれます。例えば、C2セルに25:30と表示されている場合、これを時給1,000円で計算すると、C2セルに「=C2*24*1000」と入力すれば「25500」と表示されます。これは、C2セルの値が内部的に「1.0625」(25.5時間/24時間)という数値として認識されているためです。

まとめ

Excelで24時を超える時刻を正しく表示・計算するには、セルの書式設定でユーザー定義の書式コード「[h]:mm」を使用することが不可欠です。この設定により、勤怠表での残業時間や総労働時間などが正確に計算・表示できるようになります。数式での時刻計算後、この書式設定を適用するだけで、煩雑な手作業や誤計算を防ぐことができます。今後は、この書式設定を活用して、より効率的で正確な勤怠管理を実現してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】