If you want to know how to calculate hours in Excel, you’re in the right place! ⏱️ Whether you’re managing a timesheet, tracking work hours, or calculating overtime, Excel offers simple and accurate ways to compute total hours worked — including start and end times, breaks, and even time over multiple days. In this guide, we’ll show you exactly how to do it across Windows, macOS, and Ubuntu/Linux using formulas, formatting tips, and time-saving keyboard shortcuts. ✅
🧠 Why Calculate Hours in Excel?
From freelancers and project managers to HR departments and students, Excel is used to:
- ⌚ Track start and end times
- 📊 Log total working hours
- 💼 Calculate overtime or shift durations
- 🧾 Create printable or automated timesheets
- 🔄 Convert time to decimal for payroll systems
💡 Excel treats time as a fraction of a 24-hour day, so 6 hours = 0.25 (6/24).
✅ Basic Time Calculation Formula
To calculate total hours:
=EndTime - StartTime
Example:
| Start Time | End Time | Formula | Result |
|---|---|---|---|
| 08:00 | 17:00 | =B2 - A2 | 9:00 |
📌 Format the result cell as Time (hh:mm) or [h]:mm to avoid errors over 24 hours.
✅ Include Breaks in Calculation
=EndTime - StartTime - BreakTime
Example:
| Start | End | Break | Formula | Result |
|---|---|---|---|---|
| 08:00 | 17:00 | 1:00 | =B2 - A2 - C2 | 8:00 |
✅ Always ensure all times are entered in Time Format (hh:mm).
✅ Sum Hours Across Multiple Days
When hours go over 24, Excel may reset to zero unless you use a custom format:
- Select total cell → Right-click → Format Cells
- Choose Custom > [h]:mm
This allows totals like 26:30 instead of showing 2:30.
🧠 [h]:mm shows accumulated hours across dates, not just within a day.
✅ Convert Time to Decimal Hours
Need to calculate decimal hours for payroll?
=(EndTime - StartTime) * 24
Example:
| Start | End | Formula | Result |
|---|---|---|---|
| 09:00 | 17:30 | =(B2 - A2) * 24 | 8.5 |
✅ Format result as Number to display decimals properly.
⌨️ Keyboard Shortcuts to Speed Up Time Calculations
| Task | Windows | macOS | Ubuntu/Linux (LibreOffice Calc) |
|---|---|---|---|
| Insert current time | Ctrl + Shift + ; | Cmd + Shift + ; | Ctrl + Shift + ; |
| Insert current date | Ctrl + ; | Cmd + ; | Ctrl + ; |
| Format cells | Ctrl + 1 | Cmd + 1 | Ctrl + 1 |
| Enter edit mode in cell | F2 | Ctrl + U | F2 |
| Fill formula down | Ctrl + D | Cmd + D | Ctrl + D |
⚡ Quick tip: Use Ctrl + to toggle formula view on/off in Excel.
🐧 How to Calculate Hours in LibreOffice Calc (Ubuntu/Linux)
LibreOffice Calc handles time similarly to Excel:
- Enter start and end times in hh:mm format
- Use
=End - Startfor duration - For decimals: multiply by 24
- Format cell as [HH]:MM to allow totals > 24h
✅ Formulas:
=B2 - A2
=(B2 - A2) * 24
LibreOffice uses semicolon (;) instead of commas (,) in some regions.
🧼 Common Time Calculation Issues
| Issue | Solution |
|---|---|
| Result shows ###### | Column too narrow → expand width |
| Incorrect total time | Use [h]:mm format to avoid reset at 24h |
| Wrong decimal output | Ensure cell format is Number |
| Errors in subtraction | Double-check cell format is Time |
| Negative time showing ###### | Ensure EndTime > StartTime |
🧠 If tracking overnight shifts, use =End + 1 - Start to include next day.
📊 Real-World Use Cases
| Use Case | Description |
|---|---|
| Employee time tracking | Start/end/breaks → total hours and overtime |
| Freelance billing | Log hours worked per project |
| Call center logs | Analyze time spent per shift or task |
| School schedules | Track class hours, breaks, and totals |
| Healthcare shifts | Track nursing or doctor hours per patient |
FAQs
How do I calculate hours worked in Excel?
Use =EndTime - StartTime, format the result as [h]:mm.
How can I calculate hours worked including breaks?
Use =EndTime - StartTime - BreakTime.
How do I convert Excel time to decimal?
Multiply the time difference by 24: =(EndTime - StartTime) * 24.
Does Excel handle times over 24 hours?
Yes, but you must format the cell as [h]:mm.
How can I calculate time in LibreOffice Calc?
Use the same formulas as Excel; format cells as [HH]:MM.
Can I track hours across multiple rows?
Yes, sum all time values with =SUM(D2:D10) and format as [h]:mm.
✅ Conclusion: Calculate Hours in Excel with Precision
Knowing how to calculate hours in Excel gives you the power to track time, manage work logs, and streamline reporting with total accuracy. ⏱️ Whether you’re handling payroll, freelance projects, or academic planning, Excel’s formulas and formatting make time management easy and reliable.
Best of all, these methods work seamlessly on Windows, macOS, and Ubuntu/Linux, with powerful shortcuts to speed up your workflow. ⌨️ Take control of your time — one formula at a time!
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment