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.

conversion of time figures into decimal

6 Responses

  1. The time and the minutes are 2 separate columns. How do you make it so that the result is in one column?

  2. when calculating hours worked, the formula doesn't recognize more than 24 hours. it keeps giving me a value of 2 for the hours, but had no problem when the total was 18. the minutes column was orginally formatted to be expressed in dollar amounts. i reverted it back to normal, but it behaves the same. what is going wrong?

  3. I am confused. You show the time in one column, which most of the time, is the case. But, then you go on to talk about the hours and minutes being in different columns. Is there an easy way to separate the hours and minutes into two columns?

Leave a Reply

Your email address will not be published. Required fields are marked *

Get the 5 Ways to a Build Successful firm eBook