If you have time stored as 1:30 but want it as a decimal (1.50), or if you want to convert time to minutes/seconds this is the tutorial for you! Converting time into decimals is easy once you know these three ways to do it.
Arithmetic Way
The easiest way to convert time to decimal in Excel is to times the original value by the number of hours, seconds or minutes in a day.
- To convert to hours, multiply by 24 (number of hours in a day).
- To convert to minutes, multiply by 1440 (number of minutes in a day).
- To convert to seconds, multiply by 86400 (number of seconds in a day).
You need to first right click on your time column, select Format Cells and make sure they are set to the following:
For all of your decimal columns, right click and make sure they are formatted as number.
Now enter your formulas:
- Hours ={cell}*24
- Whole Hours =INT({cell}*24)
- Minutes ={cell}*1440
- Seconds ={cell}*86400
CONVERT Function Way
You can achieve the same result using the inbuilt Excel CONVERT function. Make sure your time/decimal cells are formatted the same as listed above.
Now enter your formulas:
- Hours =CONVERT({cell},”day”,”hr”)
- Minutes =CONVERT({cell},”day”,”min”)
- Seconds =CONVERT({cell},”day”,”sec”)
Formulaic Way
First, create a new column and enter the formula =HOUR({cell})+MINUTE({cell})/60. In my case cell is A2.
You also need to make sure that the cells are formatted correctly. Select the Time column, right click and select “Format Cells” and select Custom h:mm.
Do the same for your Decimals column but set it to Number with 2 decimals.
You will now have time in decimals.
If this blog helped you, please share it, because it may also help your friends.
