EXCEL GUIDE

Decimal time in Excel

Excel stores time as a fraction of a day. Multiply a time value by 24 to get decimal hours; divide decimal hours by 24 to turn them back into an Excel time value.

Convert hours and minutes to decimal hours

If cell A2 contains 8:45, enter this formula in B2:

Format B2 as a Number with two decimal places. The result is 8.75. If it still looks like a clock time, change the result cell's format from Time to Number or General.

Convert decimal hours back to time

If A2 contains the number 8.75, enter this formula in B2:

Format B2 as h:mm to show 8:45. For a duration that can exceed 24 hours, use the custom format [h]:mm instead. Without brackets, Excel displays clock time and wraps after 24 hours.

Calculate worked hours

For a start time in A2 and an end time in B2 on the same day, subtract the start and multiply by 24:

If an unpaid break of 30 minutes is entered as 0:30 in C2, use:

Format the result as a Number. If the shift crosses midnight, include the dates in both start and end cells so the subtraction uses the correct days.

Why does Excel show 0.5 for 12:00?

Excel stores one day as 1. Noon is halfway through a day, so a cell containing 12:00 has the underlying numeric value 0.5. Multiply by 24 and you get 12 decimal hours.

One common mistake is reading 4.25 hours as 4 hours 25 minutes. The .25 represents one quarter of an hour, or 15 minutes.

Rounding several entries without losing time

Suppose A2, A3 and A4 each contain a duration of 0:20. Summing the original time values and then multiplying by 24 gives exactly one decimal hour:

The result is 1.00. If you round each 20-minute entry to 0.33 hours first and then add those rounded numbers, the result is 0.99. Keep the precise values for calculations and set the display format of the final cell to two decimal places. Your workplace or payroll system may apply a specified rounding rule; use that rule when preparing official records.

For more worked shift examples, see our guide to decimal hours on timesheets.