Excel and Google Sheets Time Conversion
On this page
Spreadsheet time values are commonly stored as fractions of a day. Multiply a true spreadsheet duration by 24 to return decimal hours, or divide decimal hours by 24 to create a duration value.
Formula reference
| Starting data | Formula | Result type |
|---|---|---|
| Total minutes in A2 | =A2/60 | Decimal hours |
| Total minutes in A2 | =A2/1440 | Spreadsheet duration |
| Duration or HH:MM value in A2 | =A2*24 | Decimal hours |
| Decimal hours in A2 | =A2/24 | Spreadsheet duration |
| Hours in B2 and minutes in C2 | =B2+C2/60 | Decimal hours |
| Hours B2, minutes C2, seconds D2 | =B2+C2/60+D2/3600 | Decimal hours |
Use the Decimal Hours Calculator to verify a sample before filling a formula down a large sheet.
Convert total minutes to decimal hours
If A2 contains 345, use =A2/60. The result is 5.75. This output is a number, not a clock display.
Convert an HH:MM duration to decimal hours
If A2 contains a real spreadsheet duration such as 7:45, use =A2*24. Format the result as Number. The result is 7.75.
If A2 contains text rather than a recognized duration, the formula may fail or produce an unexpected result. Normalize the data before calculation.
Convert decimal hours back to a duration
If A2 contains 7.75, use =A2/24 and format the result as [h]:mm. The display becomes 7:45.
The brackets matter. Microsoft documents the [h]:mm style for displaying totals above 24 hours. Without brackets, 40:15 can wrap and display as 16:15.
Add durations above 24 hours
Sum true duration values with =SUM(A2:A8), then apply [h]:mm or [h]:mm:ss. Do not use the HOUR function to recover accumulated hours because it returns the hour component within a day rather than the entire elapsed total.
Start and end times
For same-date times in A2 and B2, =MOD(B2-A2,1)*24 returns decimal hours and handles a simple overnight crossing. When actual dates are available, subtract full date-time values instead of relying on MOD so multi-day durations remain explicit.
Use the Time Duration Calculator to verify overnight shifts, breaks, and multi-day examples.
Prevent common spreadsheet errors
- Confirm whether an imported value is text, a number, a time of day, or a duration.
- Use 7:45 for HH:MM and 7.75 for decimal hours.
- Do not type 7.45 when you mean 7 hours 45 minutes.
- Preserve the exact underlying value and round only the displayed cell unless the business rule says otherwise.
- Label columns with the expected format.
- Test a duration above 24 hours before deploying a timesheet template.
Google Sheets note
Google Sheets also treats dates, times, and durations as numeric day values. Use Duration or a suitable custom number format for elapsed time. Interface names and locale behavior can differ, so validate the formula in a copy of the actual workbook.
Frequently asked questions
Why does my total reset after 24 hours?
The cell is formatted like a time of day. Use an accumulated-duration format such as [h]:mm.
Why does multiplying by 24 work?
One whole spreadsheet day is stored as 1. Multiplying the day fraction by 24 expresses the value in hours.
How do I turn 90 minutes into 1:30?
Use =A2/1440 when A2 contains 90, then format the result as [h]:mm.
Sources
Use this resource
Keep exploring