Time to Decimal in Excel: 5 Formulas That Actually Work (+ Google Sheets)

Why Does Excel Show Time as a Decimal Like 0.354 Instead of Hours?

Excel's date serial number system goes back to its very first release (Excel 1.0 for Mac, 1985), copied from Lotus 1-2-3 for file compatibility when Excel came to Windows in 1987. Every date and time in this system is stored as a plain number. The number 1.0 = January 1, 1900. The number 0.5 = noon. So 8:30 AM is stored as 0.354167, because 8.5 hours divided by 24 hours equals 0.354167. This behavior is the same in Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and everything older.

When you format a cell as h:mm, Excel translates that decimal back into something readable. Looks fine. But the underlying number is still a fraction. The problem hits when you try to use that value in a pay formula. You end up multiplying someone's wage by 0.354 instead of 8.5. That's a 24x error, and it's embarrassingly easy to miss until someone's paycheck comes out wrong.

You always just multiply by 24. That's the whole trick. Every formula in this guide is a variation of that one idea.

How Do You Convert an Excel Time Value to Decimal Hours?

When your time is stored as an Excel time value (formatted as h:mm or HH:MM)

You typed times directly into Excel, they look right (8:30, 7:45), and now you need decimal hours for payroll, a client invoice, or a time card. This is the case you'll hit most often. One formula does it.

Multiply by 24. Excel sees 8:30 as 0.354167. 0.354167 times 24 is 8.5. Format the result cell as "Number" with 2 decimal places and you're done.

Works the same way in Google Sheets, no changes needed. If you'd rather see the math spelled out explicitly, there's a longer version that extracts hours and minutes separately:

This pulls the hour component directly and divides the minutes by 60 before adding them together. More typing, same result. It's useful if you're showing someone else how the conversion works, or troubleshooting a formula that's misbehaving. Microsoft's docs cover how Excel's time serial number system works if you want to go deeper.

How Do You Convert a Text Time String Like "7:45" to Decimal Hours in Excel?

When your time is stored as text like "7:45" or "07:45"

Time data exported from other tools, pasted from a CSV, or pulled from a database often lands in Excel as plain text rather than an actual time value. You can usually tell because the values are left-aligned in their cells, while real Excel times sit right-aligned. Or just try =A2*24 and see if it throws an error. If it does, you've got text.

Excel's TIMEVALUE function handles this. See Microsoft's TIMEVALUE docs for the full reference.

TIMEVALUE converts a text string into Excel's serial time format, a fraction between 0 and 1. Multiply by 24 and you've got decimal hours. It recognizes "7:45", "07:45", "7:45 AM", "19:30" and most other standard formats. If your data is consistently formatted, just use this.

But sometimes TIMEVALUE chokes on a weird format or returns a #VALUE! error. In that case, parse the text yourself with this longer formula:

This finds the colon, grabs everything to the left (hours), grabs the two characters to the right (minutes), converts both to numbers, and combines them. It's ugly but it works on anything TIMEVALUE refuses to touch.

How Do You Display Decimal Hours as HH:MM Format in Excel?

Convert decimal hours (like 7.75) to display as 7:45 in a cell

Going the other direction: you have a decimal like 7.75 and you want the cell to show 7:45. Divide by 24 to convert back to Excel's time fraction, then apply a custom number format. The formula is simple. The formatting step is where people get tripped up.

Dividing by 24 gets you back to Excel's internal time fraction, but the cell will still display a number until you tell Excel to format it differently. Four steps:

1. Select the cell or column that contains your =A2/24 formula results.
2. Press Ctrl+1 (Windows) or Cmd+1 (Mac) to open the Format Cells dialog.
3. Click the Number tab, then scroll to the bottom of the Category list and select Custom.
4. In the Type field, enter [h]:mm and click OK.

Don't skip the square brackets around [h]. Without them, Excel uses h:mm, which resets to zero at midnight. So 25.5 hours shows up as 1:30 instead of 25:30, which looks like a data error and will confuse everyone. The [h]:mm format keeps accumulating past 24, which is what you actually need for weekly totals.

How Do You Sum Time in Excel and Get a Decimal Total?

Sum a week of HH:MM times and display the total in decimal

For a weekly timesheet you usually want two totals: one in HH:MM for readability, and one in decimal hours for the pay calculation. Set up both in your totals row. They pull from the same column.

Put =SUM(B2:B8) in your totals cell, then format it as [h]:mm (see Formula 3 for how). That gives you a readable "38:45" total. For the pay calculation, add a second totals cell next to it:

How Do You Calculate Overtime Pay from Decimal Hours in Excel?

Calculate regular pay and overtime pay from decimal hours

Once you have decimal hours, pay calculations are just arithmetic. The U.S. federal overtime threshold is 40 hours per week at 1.5x the regular rate. The formulas below assume your total decimal hours are in cell A2 and your hourly rate is in cell C2.

MIN(A2,40) caps at 40, so anyone under that threshold just gets their actual count. MAX(A2-40,0) gives you the overtime portion and floors it at zero so you don't end up with negative overtime for a 38-hour week.

How Do You Build a Complete Time Tracking Spreadsheet in Excel?

Here's how to put it all together into a working 5-column weekly timesheet. Open a blank spreadsheet and set these headers in row 1:

Column A takes your dates in whatever format Excel recognizes automatically. Columns B and C get your start and end times. Type "8:30" and press Enter; Excel should auto-format the cell as a time value. If it shows a decimal instead, select the column and format it as h:mm via Format Cells (Ctrl+1).

Column D is your duration. The formula is just =C2-B2. That gives the elapsed time as an Excel time fraction. Format column D as [h]:mm so it displays cleanly as hours and minutes.

Column E is your decimal hours: =D2*24, formatted as Number with 2 decimal places. This is the number you multiply by an hourly rate. Most payroll systems and U.S. labor law calculations want time expressed this way, where 30 minutes = 0.5 and 45 minutes = 0.75. Copy both D and E formulas down for each row in the week.

In your totals row (say row 9), put =SUM(D2:D8) formatted as [h]:mm for the display total, and =SUM(E2:E8) for the decimal total. If you want gross pay, add a rate cell somewhere (G2 works) and use =E9*G2. For overtime, plug E9 into the Formula 5 set above. Freeze the header row under View, then save the file as a template (.xltx) so you can reuse the structure each week without overwriting your data. Add a conditional formatting rule to flag any day over 10 hours. It catches typos before they hit payroll.

Do These Time-to-Decimal Formulas Work in Google Sheets?

Yes, all five formulas work in Google Sheets without changes. The time model is identical: times are stored as fractions of a day, so =A2*24, =TIMEVALUE(A2)*24, =SUM(B2:B8)*24, and the overtime formulas all transfer directly.

Sheets is more aggressive about auto-detecting time values when you paste from outside sources. Text strings that need TIMEVALUE in Excel might just work with =A2*24 in Sheets. Test on your actual data and see. To apply the [h]:mm custom format in Sheets, go to Format, then Number, then Custom date and time, and type [h]:mm directly.

Quick Formula Reference Table

All five formulas plus a couple of edge cases you'll eventually run into. The last two rows (hours in separate cells, and HHMMSS strings with seconds) aren't covered in the main sections but come up often enough to be worth having.

Still Hate Excel Time Math? Use a Dedicated Calculator

If you're doing this conversion every week for a whole team, a time-tracking app solves the problem at the source. These tools record hours in decimal format as people clock in and out, so you never have to touch a formula. Your export is already payroll-ready. Two we've actually used:

Frequently Asked Questions