Hour Calculator Excel Formula: How To & Examples
This page explains how to build an hour calculator in Microsoft Excel: you enter start and end times (and optionally break duration), and the formula returns total hours worked, either as a decimal number or in h:mm format. The core formula is =IF(end>start, end-start, 1+end-start)*24 for decimal hours, or simply =end-start formatted as [h]:mm for clock-time display. Below we cover the exact formulas, formatting, common pitfalls, and how to handle overnight shifts.
Why Excel Needs a Special Formula for Hours
Excel stores times as fractions of a 24-hour day. 6:00 AM is stored as 0.25, 12:00 noon as 0.5, and 6:00 PM as 0.75. When you subtract two times, Excel returns another fraction (e.g., 0.125 = 3 hours). To display that as “3:00” you use time formatting; to get a plain number like 3.0 (for payroll or billing) you multiply by 24. The confusion between these two representations causes most hour-calculator errors.
The Basic Formula (Same-Day Shift)
If the start and end times are on the same day, the simplest formula is:
- Decimal hours:
=(end_time - start_time)*24— result is a number like 7.5 for 7 hours 30 minutes. - Clock format:
=end_time - start_time— then format the cell as[h]:mmto show total hours (e.g., 7:30), not just the hour part.
The [h] in square brackets is essential. Without brackets, a shift of 26 hours would display as 2:00 (because 26 hours = 1 day + 2 hours). With brackets, it correctly shows 26:00.
Overnight Shift Formula (End Time Less Than Start Time)
For shifts that cross midnight (e.g., 10:00 PM to 6:00 AM), a simple subtraction gives a negative value. The standard fix is:
- Decimal hours:
=IF(end_time > start_time, end_time - start_time, 1 + end_time - start_time)*24 - Clock format:
=IF(end_time > start_time, end_time - start_time, 1 + end_time - start_time)formatted as [h]:mm.
The 1 + adds a full day (24 hours) when the shift wraps past midnight. This works because Excel treats 1 as one day.
Including Break Time
To subtract an unpaid break, just subtract the break duration from the total:
- Decimal hours:
=((end_time - start_time) - break_time)*24(same-day) or=IF(end>start, end-start, 1+end-start)*24 - break_minutes/60(overnight). - Clock format:
=IF(end>start, end-start, 1+end-start) - break_timewhere break_time is entered as a time value (e.g., 0:30 for 30 minutes).
For example, if start is 9:00 AM, end is 5:30 PM, and break is 0:30, the decimal formula gives =((5:30 PM - 9:00 AM) - 0:30)*24 = (8.5 - 0.5)*24 = 8.0 hours.
Formatting Cells Correctly
Most hour-calculator errors come from wrong cell formatting. Follow these rules:
- Start and end times: enter as time values (e.g., 9:00 AM or 9:00). Apply the Time format (Home → Number Format → Time).
- Break time: enter as a time value too (e.g., 0:30 for 30 minutes).
- Result cell (decimal hours): format as Number with 2 decimal places.
- Result cell (clock format): use Custom format
[h]:mm(right-click → Format Cells → Custom → type[h]:mm).
If you see a time like 12:00 AM when you expected a number, the cell is formatted as Time — change it to Number.
Common Mistakes and How to Avoid Them
- Using h:mm instead of [h]:mm — h:mm only shows hours within a day (0-23), so a 30-hour shift shows as 6:00. Always use [h]:mm for totals.
- Not multiplying by 24 — subtracting times gives a fraction of a day; to get hours you must multiply by 24.
- Ignoring overnight shifts — if you don’t use the IF formula, you get a negative value or ##### error.
- Entering times as text — if you type “6pm” without a space, Excel may treat it as text. Use a real time format (6:00 PM).
- Break entered as decimal hours — if you type 0.5 for break, it means 12 hours (0.5 day), not 30 minutes. Enter 0:30 or 30/1440.
How to Sum Multiple Shifts for a Week
Once you have each day’s total hours in a column, use =SUM(range) to get the weekly total. If the daily totals are in decimal format (e.g., 8.5), the sum is a decimal number. If they are in [h]:mm format, the sum will automatically display in [h]:mm if the cell is formatted that way. For a weekly total that exceeds 24 hours, the [h]:mm format is essential — otherwise it will wrap to the next day.
Frequently Asked Questions
How do I calculate hours worked in Excel without a break?
Use =(end_time - start_time)*24 for decimal hours, or =end_time - start_time formatted as [h]:mm. For overnight, use the IF formula: =IF(end>start, end-start, 1+end-start)*24.
Why does my hour formula show a negative number?
That happens when the end time is earlier than the start time, meaning the shift crosses midnight. Use the IF formula to add 1 (one day) when end < start. For example, =IF(B2>A2, B2-A2, 1+B2-A2).
How do I convert decimal hours (like 7.5) to h:mm format?
Multiply the decimal by 24 to get hours, then divide by 24 to get a time value. For example, to display 7.5 as 7:30, use =7.5/24 and format as [h]:mm. Or simply use the time subtraction formula and format the result as [h]:mm.
What does the [h] in [h]:mm mean?
The square brackets tell Excel to display total hours even if they exceed 24. Without brackets, “h” shows only the hour within a day (0-23). So [h]:mm is required for totals greater than 24 hours.
Can I calculate hours with a lunch break automatically?
Yes. Enter the break duration as a time value (e.g., 0:30) in a separate cell, then subtract it: =((end-start)-break)*24 for decimal hours. For overnight, use =IF(end>start, end-start, 1+end-start)*24 - break*24 (if break is in time format).
How do I handle shifts that span multiple days (like 24+ hours)?
Use the same formulas; the IF formula handles a single overnight. For shifts longer than 24 hours, you need to include the date in the start and end cells (e.g., 2025-01-01 10:00 PM). Then a simple subtraction =end-start works, and multiply by 24 for decimal hours. Format as [h]:mm to show total hours.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.