Why Excel sees 9:00 AM as 0.375

Type 9:00 into a cell. Excel stores 0.375. Noon is 0.5. Midnight is 0.0 or 1.0, depending on context.

This is not a quirk. It is the foundation of every time formula in Excel. Forget it and you get wrong answers. Remember it and everything else falls into place.

Excel treats all clock times as a fraction of a 24-hour day. 9:00 AM is 9/24 of the way through. Type 17:00 and Excel stores 0.70833. Subtract the two and Excel returns 0.33333: exactly 8 hours, because 0.33333 of a day is 8 hours.

You never see the fraction unless you change the cell format. But the fraction is always there. Format a time cell as General or Number and it appears.

Adding hours and minutes

Use the + operator. Cell A1 holds 2:00 (2 hours). Cell A2 holds 1:30 (1 hour 30 minutes). =A1+A2 returns 3:30.

Both values are fractions of a day. 2 hours equals 0.08333 of a day. 1.5 hours equals 0.0625 of a day. The sum is 0.14583 of a day, displayed as 3:30 when the cell uses a time format.

Why this breaks: A result exceeding 24 hours wraps around. 14:00 + 14:00 gives 4:00, not 28:00. Excel assumes you want the next day's clock time. Apply the custom format [h]:mm. The square brackets tell Excel to keep counting past 24. Without them, Excel resets.

Subtracting time and handling negative values

Subtraction works the same way. =B1-A1 where B1 is 17:00 and A1 is 9:00 returns 8:00.

The problem hits when you subtract a later time from an earlier one. =A1-B1 returns ########. Excel cannot display negative time in the default 1900 date system.

Three ways to fix it:

  1. Switch to the 1904 date system. File > Options > Advanced. Under "When calculating this workbook", check "Use 1904 date system". Negative times now display as -8:00. The catch: existing dates shift by four years. Skip this if your workbook holds dates before 1904, though that is rare. It can still break date calculations elsewhere.

  2. Use an IF formula to avoid negatives. =IF(B1<A1, (B1+1)-A1, B1-A1) adds one full day (1.0) when the end time is earlier than the start time. This assumes the end time belongs to the next day. Useful for shift work that crosses midnight.

  3. Convert to decimal hours first. =(B1-A1)*24 gives hours as a decimal. A negative value displays correctly because decimals can be negative. This is the cleanest fix for most people.

Converting time to decimal hours

To turn 2:30 (2 hours 30 minutes) into 2.5 hours, multiply by 24.

=A1*24

If A1 contains 2:30, the result is 2.5. Format the result as Number with decimal places.

Why 24? 1.0 in Excel equals 1 full day, which is 24 hours. Multiplying by 24 converts the day-fraction into hours.

Common mistake: Typing =A1*60 expecting hours. That gives 150 minutes, not 2.5 hours. For minutes, multiply by 1440 (24 × 60). For hours, multiply by 24.

Decimal hours reference table:

Minutes Decimal hours
0 0.00
5 0.08
10 0.17
15 0.25
20 0.33
25 0.42
30 0.50
35 0.58
40 0.67
45 0.75
50 0.83
55 0.92

Exact fractions: 1 minute = 0.0167 hours (1/60). 7 minutes = 0.1167 (7/60). 13 minutes = 0.2167 (13/60). 20 minutes = 0.3333 (20/60). 40 minutes = 0.6667 (40/60). 45 minutes = 0.7500 (45/60).

Calculating elapsed time across midnight

A shift starting at 22:00 and ending at 06:00 the next morning breaks simple subtraction. 06:00 - 22:00 produces a negative number.

Use the standard formula: =IF(end<start, end+1-start, end-start)

The +1 adds one full day (24 hours) to the end time. Excel treats 06:00 as 0.25 and 06:00 + 1 as 1.25. Subtract 0.91667 (22:00) and you get 0.33333, which is 8 hours.

For military times stored as numbers (e.g., 2200 for 10:00 PM), convert them first with =TEXT(A1,"00:00") then apply the formula above. Better yet, store all times as actual time values, not text or integers.

How to sum timesheets past 24 hours in Excel

A weekly timesheet with five 8-hour shifts totals 40 hours. =SUM(A1:A5) shows 16:00. That is 40 hours minus 24 hours, displayed as 4:00 PM the next day.

Fix: Apply custom format [h]:mm. The square brackets stop Excel resetting at 24. The cell now shows 40:00.

For decimal hours instead: =SUM(A1:A5)*24. Format as Number.

For hours and minutes in separate columns: =INT(SUM(A1:A5)*24) for hours, =MINUTE(SUM(A1:A5)) for minutes.

Common Excel time errors and fixes

Error Cause Fix
######## Negative time or column too narrow Widen column or fix negative with 1904 system or IF formula
16:00 when you expect 40:00 Format resets at 24 hours Use [h]:mm format
0.33 when you expect 8:00 Cell formatted as Number instead of Time Format cell as Time
8.5 when you expect 8:30 Decimal hours displayed instead of clock time Use h:mm format or convert with =TEXT(A1/24,"h:mm")
#VALUE! Text or non-time value in formula Check cells contain valid time entries, not strings like "9am"
Wrong result after DST change Excel does not track DST Store all times in UTC or use absolute time references

The most common mistake: Typing 8.30 instead of 8:30. Excel reads 8.30 as a decimal number, not a time. Use colons, not decimal points.

Text-to-time fix: Imported times stored as text ("8:30") need =TIMEVALUE(A1). If the import uses AM/PM ("8:30 AM"), =TIMEVALUE(A1) still works.

Frequently asked questions

Why does Excel show 12:00 AM when I type 0:00?

Excel treats 0:00 as midnight, displayed as 12:00 AM in 12-hour format. Switch to 24-hour format (HH:mm) to see 00:00.

How do I calculate total minutes from hours and minutes?

Multiply the time value by 1440. =A1*1440 converts 1:30 to 90 minutes.

Can Excel handle negative time without changing the date system?

Yes, with workarounds only. =IF(B1<A1, "-"&TEXT((A1-B1),"h:mm"), TEXT((B1-A1),"h:mm")) displays a text string that looks like negative time. This breaks further calculations.

Why does my time formula return 0.00?

Your cell is formatted as General or Number. Format it as Time (h:mm) to see the clock value, or multiply by 24 to see decimal hours.

What format should I use for timesheets?

Use [h]:mm for total hours worked. Use h:mm AM/PM for clock-in and clock-out times. Use 0.00 for decimal hours if your payroll requires it.

What to do next: To add up a list of durations, use the Time Addition Calculator. To subtract time and find a difference, the Time Subtraction Calculator handles midnight crossover automatically. For converting minutes to decimal hours, the minutes to decimal hours guide has the full chart.