「勤務時間を合計したいのに、24時間を超えると表示が変になる…」「分単位で計算したいけど、どうやればいい？」「残業時間の集計がうまくいかない」
そんな悩みを持つ人は多いのではないでしょうか？Excelで時間計算を行う際、多くの人が「なぜ思った通りの結果にならないの？」と困惑します。
実は、Excel（エクセル）では時間の足し算も簡単に行えますが、表示形式の設定や合計方法によっては、思ったような結果が出ないことがあります。特に、24時間を超える合計や、残業時間の計算では、適切な設定が必要です。
この記事では、時間の足し算の基本から、24時間を超える場合の設定、分・秒の扱い、実際の勤怠管理での活用方法まで、初心者でもすぐに実践できる内容を丁寧に解説します。
Excelでの時間の基本知識
時間の内部表現
Excelの時間管理システム
シリアル値での管理
- Excelは時間を小数で管理
- 1日 = 1.0
- 1時間 = 1/24 ≈ 0.041667
- 1分 = 1/1440 ≈ 0.000694
具体例
12:00（正午） = 0.5
6:00（午前6時） = 0.25
18:00（午後6時） = 0.75
時間入力の基本ルール
標準的な入力形式
8:30 # 8時間30分
1:15:30 # 1時間15分30秒
0:45 # 45分
入力時の注意点
- コロン（:）で区切って入力
- 先頭の0は省略可能
- 24時間表記で入力
時間データの表示形式
基本的な表示形式
主要な時間形式
h:mm # 8:30
hh:mm # 08:30
h:mm:ss # 8:30:15
[h]:mm # 32:30（24時間超対応）
表示形式の設定方法
手順
- 時間が入力されたセルを選択
- 右クリック →「セルの書式設定」
- 「表示形式」タブ →「時刻」カテゴリ
- 適切な形式を選択
基本的な時間の足し算
SUM関数を使った基本計算
基本的な手順
Step 1: 時間データの入力
A1: 2:30 # 2時間30分
A2: 1:45 # 1時間45分
A3: 3:15 # 3時間15分
Step 2: 合計の計算
A4: =SUM(A1:A3)
結果
A4: 7:30 # 7時間30分
より実用的な例
勤務時間の合計
| 日付 | 勤務時間 |
|---|---|
| 月曜日 | 8:00 |
| 火曜日 | 7:30 |
| 水曜日 | 8:15 |
| 木曜日 | 7:45 |
| 金曜日 | 8:30 |
| 合計 | =SUM(B2:B6) |
表示形式の重要性
よくある問題
小数で表示される場合
期待値: 8:30
実際の表示: 0.354166667
原因 セルの表示形式が「標準」または「数値」に設定されている
解決方法
表示形式の変更
- 合計セルを選択
- 右クリック →「セルの書式設定」
- 「時刻」カテゴリを選択
- 「13:30」などの形式を選択
ショートカット方法
Ctrl + Shift + @ # 時間形式に変更
24時間を超える時間の合計
問題の発生
なぜ24時間を超えると問題になるのか
Excelの標準動作
入力: 26:30
表示: 2:30 # 24時間が引かれて表示
実際の問題例
月曜: 8:00
火曜: 9:00
水曜: 9:30
合計: 26:30 → 2:30と表示される（間違い）
解決方法：角括弧の活用
ユーザー定義形式での設定
Step 1: セルの書式設定を開く
- 合計セルを右クリック
- 「セルの書式設定」を選択
Step 2: ユーザー定義を設定
- 「表示形式」タブ
- 「ユーザー定義」を選択
- 種類欄に
[h]:mmと入力
角括弧の効果
[h]:mm # 時間を累積表示（24時間超対応）
[mm]:ss # 分を累積表示
[ss] # 秒を累積表示
実際の設定例
週間勤務時間の合計
表示形式: [h]:mm
結果: 42:15 # 42時間15分
月間勤務時間の合計
表示形式: [h]:mm
結果: 168:30 # 168時間30分
時間差の計算と合計
開始時間と終了時間から勤務時間を計算
基本的な計算方法
データ例
| 開始時間 | 終了時間 | 勤務時間 |
|---|---|---|
| 9:00 | 17:30 | =B2-A2 |
| 8:30 | 18:00 | =B3-A3 |
| 9:15 | 17:45 | =B4-A4 |
結果
| 開始時間 | 終了時間 | 勤務時間 |
|---|---|---|
| 9:00 | 17:30 | 8:30 |
| 8:30 | 18:00 | 9:30 |
| 9:15 | 17:45 | 8:30 |
| 合計 | 26:30 |
日をまたぐ場合の計算
夜勤の場合
開始: 22:00
終了: 6:00（翌日）
勤務時間: =IF(終了時間<開始時間, 終了時間+1-開始時間, 終了時間-開始時間)
具体例
=IF(B2<A2, B2+1-A2, B2-A2)
結果: 8:00 # 22:00から翌6:00まで
休憩時間を除いた計算
休憩時間を考慮した勤務時間
計算式
実働時間 = 終了時間 - 開始時間 - 休憩時間
データ例
| 開始 | 終了 | 休憩 | 実働時間 |
|---|---|---|---|
| 9:00 | 18:00 | 1:00 | =B2-A2-C2 |
結果
実働時間: 8:00 # 9時間 - 1時間休憩 = 8時間
分・秒単位での詳細計算
秒まで含む時間計算
入力と表示
入力例
1:23:45 # 1時間23分45秒
0:15:30 # 15分30秒
2:45:15 # 2時間45分15秒
表示形式
[h]:mm:ss # 時間:分:秒（累積表示）
精密な時間計算
ストップウォッチ的な計算
| 開始時間 | 終了時間 | 経過時間 |
|---|---|---|
| 14:23:15 | 16:45:30 | =B2-A2 |
結果（秒まで表示）
2:22:15 # 2時間22分15秒
時間の単位変換
時間を分に変換
計算式
=時間セル * 1440 # 1日=1440分
実例
2:30 → 150 # 2時間30分 = 150分
時間を秒に変換
計算式
=時間セル * 86400 # 1日=86400秒
実例
1:30:45 → 5445 # 1時間30分45秒 = 5445秒
実用的な勤怠管理システム
基本的な勤怠表の作成
日次勤怠管理
表構成
| 日付 | 出勤時間 | 退勤時間 | 休憩時間 | 実働時間 | 残業時間 |
|---|---|---|---|---|---|
| 7/1 | 9:00 | 18:30 | 1:00 | =C2-B2-D2 | =MAX(0,E2-8:00) |
数式の説明
実働時間: =C2-B2-D2
残業時間: =MAX(0,E2-TIME(8,0,0))
週間・月間集計
週間合計
週間実働: =SUM(E2:E8)
表示形式: [h]:mm
月間集計
月間実働: =SUM(E2:E32)
月間残業: =SUM(F2:F32)
表示形式: [h]:mm
高度な勤怠計算
フレックスタイム制の計算
コアタイム違反のチェック
=IF(OR(B2>TIME(10,0,0),C2<TIME(15,0,0)),"違反","正常")
清算期間での時間管理
必要時間: 160:00
実働時間: =SUM(実働時間範囲)
過不足: =実働時間-TIME(160,0,0)
変形労働時間制
週40時間を超える部分を残業として計算
週間実働: =SUM(週間実働範囲)
週間残業: =MAX(0,週間実働-TIME(40,0,0))
よくある問題と解決方法
表示に関する問題
セルに「######」が表示される
原因 列幅が狭くて時間が表示しきれない
解決方法
1. 列境界をダブルクリックで自動調整
2. 手動で列幅を広げる
3. 表示形式を短い形式に変更
小数で表示される
原因 セルの表示形式が「標準」や「数値」
解決方法
1. セルを選択
2. 右クリック → セルの書式設定
3. 「時刻」カテゴリを選択
計算に関する問題
負の時間が表示されない
原因 Excelは負の時間を表示できない
解決方法
1. ファイル → オプション → 詳細設定
2. 「1904年日付システムを使用する」をチェック
3. または絶対値で表示: =ABS(計算式)
日をまたぐ計算がおかしい
問題例
開始: 23:00
終了: 2:00
結果: -21:00（おかしい）
解決方法
=IF(終了<開始, 終了+1-開始, 終了-開始)
または
=MOD(終了-開始, 1)
応用テクニック
条件付きの時間計算
残業代の自動計算
基本給の時間単価計算
時間単価 = 基本給 ÷ (月間所定労働時間)
残業代 = 残業時間 × 時間単価 × 1.25
数式例
=残業時間*24 * 時間単価 * 1.25
深夜割増の計算
22:00-5:00の深夜時間を計算
Function 深夜時間計算(開始時間, 終了時間)
Dim 深夜開始 As Double
Dim 深夜終了 As Double
深夜開始 = TimeValue("22:00")
深夜終了 = TimeValue("5:00")
' 複雑な条件分岐で深夜時間を計算
' （詳細な実装が必要）
End Function
時間データの分析
勤務パターンの分析
平均勤務時間
=AVERAGE(勤務時間範囲)
表示形式: [h]:mm
最大・最小勤務時間
最大: =MAX(勤務時間範囲)
最小: =MIN(勤務時間範囲)
遅刻・早退の集計
遅刻回数
=COUNTIF(出勤時間範囲,">"&TIME(9,0,0))
早退回数
=COUNTIF(退勤時間範囲,"<"&TIME(17,30,0))
効率化のためのテクニック
関数を使った自動化
WORKDAY関数での営業日計算
営業日のみの勤務時間集計
=SUMPRODUCT((WEEKDAY(日付範囲,2)<=5)*実働時間範囲)
条件付きSUM
特定条件での時間合計
=SUMIF(部署列,"営業",勤務時間列)
=SUMIFS(残業時間列,日付列,">="&開始日,日付列,"<="&終了日)
VBAでの高度な処理
勤怠データの一括処理
Sub 勤怠計算()
Dim i As Long
Dim 最終行 As Long
最終行 = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To 最終行
' 実働時間計算
Cells(i, "E").Formula = "=C" & i & "-B" & i & "-D" & i
' 残業時間計算
Cells(i, "F").Formula = "=MAX(0,E" & i & "-TIME(8,0,0))"
Next i
' 表示形式を設定
Range("E:F").NumberFormat = "[h]:mm"
End Sub
セキュリティと正確性
データの整合性チェック
入力値の妥当性確認
データ検証の設定
1. セル範囲を選択
2. データ → データの入力規則
3. 設定内容:
- 入力値の種類: 時刻
- データ: 次の値の間
- 最小値: 0:00
- 最大値: 23:59
計算結果の検証
チェック用の計算
検証用合計 = 手動計算での確認
差異チェック = ABS(自動計算 - 検証用合計) < 0.001
まとめ
Excelでの時間計算は、適切な設定と理解があれば非常に強力なツールになります。勤怠管理から個人のタイムトラッキングまで、幅広い用途で活用できます。
重要なポイント
基本設定
- SUM関数での時間合計
- 適切な表示形式の設定（特に[h]:mm）
- 24時間超の累積表示対応
実用的な計算
- 時間差の計算（終了時間 – 開始時間）
- 休憩時間を含む実働時間計算
- 残業時間の自動算出
高度な活用
- 条件付きの時間集計
- VBAによる自動化
- データ検証による品質管理
コメント