When working with time values in Excel, there may be times when you need to convert time to decimal digits (such as hours or minutes, or seconds). Because values in Time format cannot be used in calculations, so we need to convert them to decimal.
Excel has three different methods to convert time to decimal – by using arithmetic operations or CONVERT function or a combination of three different time functions, i.e., HOUR, MINUTE, and SECOND. This article demonstrates all three methods that you can use to convert time to decimal numbers in Excel.
Converting Time to Decimal Numbers in Excel
For instance, if you have standard time as 5:40:22 PM, you may like to convert it to decimal numbers:
- Number of hours as 5
- Number of minutes as 40
- Number of seconds as 22
To do that use any of the following three methods to change time to hours, minutes, or seconds.
Convert Time to Decimal Number Using Arithmetic Operation
This section demonstrates how to convert time to a number of hours, minutes, and seconds using arithmetic calculations in Excel.
It’s really easy to convert time to decimal numbers using arithmetic operations, all you have to do is multiply the time value by the total number of hours, seconds, or minutes in a day.
To do that, first, you have to know how many hours, minutes, and seconds are in a day:
- 24 hours in 1 day
- 60 minutes in 1 hour
- 60 * 24 (hours) = 1,440 minutes in 1 day
- 60 seconds in 1 minute
- 60 * 1,440 (minutes) or 60 * 24 * 60 = 86,400 seconds in 1 day
When you enter ‘12:00’ in Excel, Excel automatically detects this entry as ‘h:mm’. And if you change the format of that value to ‘Number’, you’ll get ‘0.50’.
This is because in Excel ‘24 hrs is equal to 1’. That is why ‘12:00’, when converted to ‘Number’ turns to 0.50 (12/24).
Convert Time to Hours in Excel
If you want to convert the standard time to a number of hours using arithmetic operations, just multiply the time value by 24, i.e. with the number of hours in a day.
Let’s say you have a time in cell A2 as 12:00 PM and you wish to convert it into hours, use this formula:
=A2*24Where A2 is where the time value is.
Don’t be alarmed if you got ‘12:00 AM’ at first. When you multiply a time value in Excel, it will return the result in the same time format, not in decimal.
To fix this, apply the ‘General’ or ‘Number’ format to the result. Go to the ‘Home’ tab, click on the Number Format drop-down list and select ‘General’ or ‘Number’. The ‘General’ format displays the number as the whole number (integer) while the ‘Number’ format displays it as a decimal with two decimal places.
Let us explain how this works, dates and times are always stored as numbers in Excel but they are formatted to look like time. As mentioned before 1 day (24 hours) is equal to 1 in Excel, so each hour is saved as 1/24.
So when you enter the value 12:00 PM, Excel stores it as the value ‘0.50’ (12/24). If you multiply the time by 24, it will offer you the number of hours that have elapsed on the day (out of 24 hours).
Also if you have a time such as 2:30 PM, multiply it by 24 and you will get 14.50 (here the minutes are displayed in decimals and full hours as integers). In this case, the numeric value of 30 minutes in Excel would be 0.50 hours.
In case you convert 12.30 PM to decimals and you get 12.5, but if you only want the full hour value without minutes part, use the following formula with INT function:
=INT(A2*24)
Convert Time to Minutes in Excel
If you want to convert time to minutes, simply multiply the time value by 1440, i.e., the number of minutes in 1 day (24*60).
Let’s assume, you have the this time value in A3 as 4:45 AM and you want to convert it into minutes, then use this formula:
=A3*1440Or if you can’t remember number of minutes in a day, multiply the time by 24*60:
=A3*24*60
In the above example, ‘285’ is the total number of minutes that have passed on that day in the given time.
Convert Time to Seconds in Excel
To convert time to seconds, multiply the time value by 86,400, which is the number of seconds in 1 day (24*60*60).
Let’s say you have the time ’05:50:10 AM’ in cell A3 and you wish to convert it into seconds (decimal), then you have to use this formula:
=A3*86400 or
=A3*24*60*60
As a result, you’ll get ‘21010’ is the total number of seconds that have passed on that day in the mentioned time.
Convert Time to Decimal Number Using the CONVERT Function
Another method to perform the time to decimal conversion is to use the CONVERT function. The CONVERT function converts the given number from one unit to another.
The syntax of CONVERT function is
=CONVERT(number,from_unit,to_unit)Parameters:
number– numeric value to convertfrom_unit– the beginning unitto_unit– the ending unit
Here you’re converting time to decimal numbers, there are only 4 units you need to remember:
- “day” – days
- “hr” – hours
- “mn” – minutes
- “sec” – seconds
This function converts a numerical value (time) into hours or minutes or seconds.
Convert Time to Hours in Excel
Suppose you a time value in cell B2, then try this formula to convert time to hours:
=CONVERT(B2,"day","hr")In the above formula, “day” informs the function that the value in cell B2 is in day format and “hr” specifies it to convert it into hours.
If you’d like to get only the hour value and ignore the minute’s parts, use the below INT formula:
=INT(CONVERT(B2,"day","hr"))
Convert Time to Minutes in Excel
To convert time to minutes using the CONVERT function, insert “day” as the ‘unit to convert from’ argument and “mn” as the ‘unit to convert to’ argument in the formula:
=CONVERT(B2,"day","mn")
Convert Time to Seconds in Excel
The formula is essentially the same as in the previous two examples with the only difference is that you convert the “day” unit to the “sec” unit:
=CONVERT(B2,"day","sec")
Convert Time to Decimal Number Using Excel Time Functions
Another way to convert time to decimal numbers is by using Excel’s time functions. This method uses a bit more complex formula than the other two methods, however, its logic is quite obvious.
Functions:
HOUR(serial_number)MINUTE(serial_number)SECOND(serial_number)
The HOUR, MINUTE, and SECOND functions simply return the number of hours, the number of minutes, and the number of seconds that have elapsed in the given time, respectively.
Convert Time to Hours Using Time Functions
We need to get all the parts in hours (hours, minutes, and seconds), so you need to combine all three functions into one formula.
To convert time to hours, get the separate time units by using HOUR, MINUTE, and SECOND functions, then divide the extracted minutes value by 60 (the number of minutes in an hour) and the seconds value by 3600 (the number of seconds in an hour (60*60)), and combine the results:
=HOUR(B2)+MINUTE(B2)/60+SECOND(B2)/3600
Convert Time to Minutes Using Time Functions
To convert time to minutes, get the separate time units by using HOUR, MINUTE, and SECOND functions, then multiply hours with 60 and divide seconds by 60:
=HOUR(B2)*60+MINUTE(B2)+SECOND(B2)/60
Convert Time to Seconds Using Time Functions
To convert time to seconds, extract all the parts (hours, minutes, and seconds) in seconds, multiply hours with 3600 (60*60) and multiply minutes by 60 and add up the results:
=HOUR(B2)*3600+MINUTE(B2)*60+SECOND(B2)
That’s it.
Member discussion