Converting Time into Decimals using Excel

February 8, 2009

Something I’ve had many customers asking is how they can convert their time figures into decimals using a spreadsheet, it’s something that’s really easy to do if you know the correct formula to use.

I’m going assume that you’ve exported the data from our system so it’s in the format 1:30 (1 hour, 30 minutes), a good report to export is the Time Summary report (found on the Reports Page).

Converting your Time to a Decimal

If you have 1:30 and you want it as a decimal (1.5) then create a new column alongside your time figures then enter the formula =hour({cell})+minute({cell})/60

Cell Formula
Cell Formula

You’ll need to make sure the cells are being formatted correctly, so select your “Time” column, right click and select “Format Cells” then make sure you’ve set the cell type to Custom h:mm

Time Cell Format

Time Cell Format

Then do the same for your “Minutes” column and make sure the cell type is set to Number with no decimals

Minutes Cell Format

Minutes Cell Format

You should end up with a nice list.

endresult1