In this blog, we are going to learn different ways to convert excel time into the corresponding decimal number. We can convert time into hours, minutes, or seconds and we have got three methods for each to perform the conversion.
Method 1 – Arithmetic Method
Time in excel is internally stored as a number whose fractional part represents time and the integral part represents the date. The integral part would be zero if there is time only.
You can use this workbook for practice.
Converting Time in Hours
We can convert time into hours in the decimal number using any of the following formulas.
Let us suppose we have the following time in the format hh:mm AM/PM as follows.
To convert this into hours, you can simply multiply the value by the total number of hours in a day i.e. 24. Use the following formula in range B2:B6.
=A2*24
If the result is not the same as we got above, then you need to set the number format of range B2:B6 to general ( Select range and press the ctrl 1 key. Choose General Number format).
Converting Time to Minutes
To convert the time into minutes, you need to multiply the time with the value equal to the number of minutes in a day. One Day has 24*60 minutes.
Use the following formula in the range C2:C6.
=A2*24*60
If the result is not the same as we got, you again need to check if the number format of the resultant range is set to the general number format.
Converting Time to Seconds
To convert this time into seconds, multiply the time with a value equal to the number of seconds in a day. This is equal to 24*60*60
Use the following formula in range D2:D6.
=A2*24*60*60
Method 2 – CONVERT Function
We can use the CONVERT Function formula to convert the Time value into Hours, Minutes, and Seconds as well.
Use the following formulas in the range B2:B6, C2:C6, and D2:D6 respectively.
=CONVERT(A2,"day","hr")
=CONVERT(A2,"day","mn")
=CONVERT(A2,"day","sec")
Explanation – The CONVERT Function takes three inputs. The first argument is the value that we want to convert. The second asks for the current unit of it. It is in the day so we supply the from_unit argument as “day”. The third argument asks for to_unit. This would be “hr” for hours, “mn” for minutes, and “sec” for seconds.
Method 3 – HOUR, MINUTE, and SECOND Function
The HOUR, MINUTE, and SECOND Functions can extract the hour, minute, and second part from a time value. We can convert a time value into hours with the following formula.
=HOUR(time_value)+MINUTE(time_value)/60+SECOND(time_value)/3600
We have divided the result of the MINUTE function by 60 to convert the minute part into hours. Similarly, the result of the SECOND Function is divided by 60*60 to convert the seconds part into hours.
Now we can use the following formula for conversion in the range B2:B6. Here time_value lies in column A.
=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/60*60
Similarly, the general method to convert time into minutes is as follows.
=HOUR(time_value)*60+MINUTE(time_value)+SECOND(time_value)/60
Here the result of the HOUR function is multiplied by 60 to convert into minutes and for the same reason, the result of the SECOND Function is divided by 60 to convert seconds into minutes.
You can use the following formula in cell C2 and then copy it down the range C2:C6.
=HOUR(A2)*60+MINUTE(A2)+SECOND(A2)/60
Similarly, we can convert time into seconds with the following syntax.
=HOUR(time_value)*60*60+MINUTE(time_value)*60+SECOND(time_value)
Use the following conversion formula.
=HOUR(A2)*60*60+MINUTE(A2)*60+SECOND(A2)
This brings us to an end.
Thank you for reading.