If you are analysing the data and ever come up with a question of how do I convert the time values generally in “hh:mm:ss” format to the denial format? Then this article is for you.
Throughout this article, I curated different ways for you to convert time values from their original format (hh:mm:ss) to decimals.
Using the Arithmetic Operation and Custom Cell Formatting
The simplest of the methods to convert time to decimal format is using the simple arithmetic operation of multiplication.
The method involves multiplying the cell containing the time value by a factor to convert the value from the standard time format (hh:mm:ss) to the decimal format.
The dataset I will be using for this demo is shown below, where column A holds all the cells with time values (and formatting, of course!). In column B, the formula will show the respective decimal conversion.
In this method, you will multiply the time values from column A by 24 to convert them into the decimal format.
1. In cell B2, type the following formula and hit the Enter button.
=A2*24
You will see the decimal representation of the time value 12:05:30 as below.
2. Copy the formula and paste it across the remaining rows in the column to get the decimal time values for respective cells.
The final output looks like the one below, where all cells from column B represent the respective decimal conversion of time values from column A.
If you don’t see values like the one shown above in column B, you will need to change the format of it to Number in the Home tab. You can increase or decrease the decimals as per your need.
The logic and working of this formula is simple. Excel represents the time value as a fraction of the entire day (24 hours or one day). The multiplication by 24 converts the time value into a fraction of how many parts of the 24 hours or one day are utilized.
To explain this concept better, when you have passed 30 minutes, you have ideally passed the half-hour, which you can also represent as 0.5 in decimals.
This multiplication converts the time into the fraction of the 24 hours. Therefore, the minutes and seconds part of the time value are converted to the decimals, respectively.
Note: If you want to see the value as a whole number and not as a decimals, you can effectively use the INT function to show only the part before the decimal of this output.
The formula is as follows:
=INT(A2 * 24)
Also read: How to Change Date and Time to Date in Excel?
Using the HOUR, MINUTE, and SECOND Functions
Another way you can convert time to decimal is by using the HOUR, MINUTE, and SECOND functions.
These functions extract the respective components from the time, and then there needs to be some arithmetic operation to give you the decimal formatted time values.
The dataset I will be using for this demo is the one below.
In Excel, the time values represent the fraction of the day passed. Out of the 24 hours of the day, the hour component while converting the time to decimal stays as it is. The only arithmetic operations you need are while converting the minutes and seconds to the fraction of the day passed.
1. In cell B2, type the following formula and hit the Enter button to execute it.
=HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/3600
2. Copy the formula and paste it across the remaining rows in the column to get the decimal time values for respective cells.
The HOUR function extracts the hour component from the time value in this formula. After that, as said earlier, you need to convert the minute and second component of the time to the fraction of the hour representing the decimal part of it.
To achieve this conversion, you need to divide the minute component of the time value by 60 because there are 60 minutes in an hour.
Similarly, to convert the seconds component of the time value to the hour, you divide it by 3600 because there are 3600 seconds in an hour.
Finally, add these minutes and seconds components to the hour to convert the time value to decimals.
The final output looks like below.
Also read: How to Calculate Elapsed Time in Excel
Using the NUMBERVALUE Function
Another useful way to convert the time values into decimals is using the NUMBERVALUE function.
If you look at the syntax of the NUMBERVALUE function,
=NUMBERVALUE(text, [decimal_separator], [group_separator]).
The first argument itself suggests that it converts the text values to numeric values.
However, in reality, the function is not limited to that. It can also take the values in numeric format and then return the same numeric version.
Following is the dataset I am using for this demo.
Let’s see how this function works with the time values stored in column A.
1. In cell B2, copy and paste the following formula and hit the Enter button to convert the time value from A2 to decimal value.
=NUMBERVALUE(A2)*24
2. Copy the formula and paste it across the remaining rows in the column to get the decimal time values for respective cells.
Let’s see what happens under the hood.
Even though the values are shown as hh:mm:ss, those values are just numbers stored with the particular format for Excel. The NUMBERVALUE function interprets them as numbers and then multiplies the result by 24 hours to return the value as a fraction of the entire day.
Also read: Convert Military Time to Standard Time in Excel (Formulas)
Using VBA Code
Using the VBA to automate tasks is an efficient way to make your worksheets look formula-free.
For this section, you will work with a VBA code connected to a Button on your Excel. Whenever you click that button, it converts the time values from column B to corresponding decimals in column B.
1. Access the VBA Editor by hitting the Alt + F11 (on Mac Option + F11 or Fn + Option + F11) keys from your keyboard.
2. Click on the Insert menu and select the Module option to add a new blank module inside the Editor.
3. Copy and paste the following VBA code inside the blank module and save it using the Save button or keyboard shortcut Ctrl + S. Close the VBA Editor afterward.
Sub ConvertTimeToDecimal()
Dim timeRange As Range
Dim cell As Range
Dim hourValue As Integer
Dim minuteValue As Integer
Dim secondValue As Integer
Dim decimalValue As Double
' Define the range containing time values (A2:A11)
Set timeRange = ThisWorkbook.Sheets("Example 4").Range("A2:A11") ' Replace "Sheet1" with your sheet name
' Loop through each cell in the time range
For Each cell In timeRange
' Extract hour, minute, and second components from the time value
hourValue = Hour(cell.Value)
minuteValue = Minute(cell.Value)
secondValue = Second(cell.Value)
' Calculate the decimal value by converting time components to fractions of an hour
decimalValue = hourValue + (minuteValue / 60) + (secondValue / 3600)
' Place the decimal value in the respective cell in column B
cell.Offset(0, 1).NumberFormat = "#0.00"
cell.Offset(0, 1).Value = decimalValue
Next cell
End SubThis code creates a subroutine named ConvertTimetoDecimal. It uses the For loop to look through the entire range of A2:A11 for time values.
It separates the hour, minute, and second components from the time values. It then converts the minute and second components to the fraction of the hour from the day by dividing them by 60 and 3600, respectively.
The code finally goes one column to the right and then writes all the decimal values across cells B2:B11. Before that, it also sets the format to “#0.00” so that values only represent two decimals.
4. On the Excel sheet, Go to the Developer menu > click on the Insert dropdown > select the Button (Form control) option.
5. Use the mouse to draw the Button shape on any location of your sheet > rename it as Time to Decimal > inside the Assign Macro dialogue box, select the macro ConvertTimetoDecimal > set the “Macros in:” option to This Workbook from the dropdown > click on the OK button.
Now, your VBA code is assigned with a dedicated button.
6. Click on the Time to Decimal button to see that the respective decimal values are written in cell B2:B11.
Pro Tip: This VBA code is customizable. If you have time values in more cells from column A or have them in any other column from your sheet, you just need to change the source range in the VBA code, and it will work exactly as expected.
Also read: How to Add Hours to Time in Excel?
Using the CONVERT Function
The CONVERT Function in Excel converts values from one measuring unit to another and holds various options for this conversion. Let’s see how to use it to convert time values into decimals.
Following is the dataset I am using for this demo.
1. Copy and paste the following formula in cell B2 and hit the Enter button from your keyboard.
=CONVERT(A2, "day", "hr")
2. Copy the formula and paste it across the remaining rows in the column to get the decimal time values for respective cells.
Here, in this formula, the CONVERT function takes the value from cell A2. Excel interprets this value as the number of days from the source date 01-01-1990 00:00:00. The formula then converts it to the corresponding number of hours.
Since no date is specified explicitly in column A while defining the time values, there are no number of days but time. Which after the function is executed looks in decimal format.
Warning: If you are working on the time values where the date is specified explicitly, you have to separate the date and time components and then try to implement the CONVERT formula shown above. Otherwise, the result will just be the number of days rather than the time in decimals.
Also read: How to Add Minutes to Time in Excel?
Converting Time Values to Minutes
Until now, whatever methods I introduced in previous sections used the time-to-hour conversion. Through this section, I will focus more on how to convert the time values to decimal minutes.
Using Simple Multiplication
We all know that there are 24 hours in a day. If we convert it to minutes, there are 24 * 60 = 1440 minutes.
Multiplying the time value by these 1440 minutes will return the decimal representation of the time in minutes.
For this demo, I am using the following dataset.
1. In cell B2, copy and paste the following formula. Hit the Enter button to execute the same.
=A3*1440
2. Copy the formula and paste it across the remaining rows in the column to get the decimal time values for respective cells.
Here, in this formula, you multiply the time values from column A by 1440, which converts them to equivalent minutes.
Using the HOUR, MINUTE, and SECOND Function
To convert the time value to respective minutes in decimal format, you can paste the following formula across the rows to generate the desired output.
=HOUR(A2)*60 + MINUTE(A2) + SECOND(A2)/60
Here, the HOUR function extracts the hour part from the time value and then multiplies it by the number of minutes in an hour, i.e., 60, to convert it to minutes.
The MINUTE function extracts the minute part of the time.
Finally, the SECOND function extracts the second part from the time, and because there are 60 seconds in a minute, you divide it by 60 to convert it to the respective fraction of the minutes.
Using the CONVERT Function
The CONVERT function works very well to return the decimal value for a specific time value.
The dataset I will use for this demo is as shown below.
1. In cell B2, copy and paste the following formula. Hit Enter to execute it to return the decimal minute value.
2. Copy the formula and paste it across the remaining rows in the column to get the decimal time values for respective cells.
The CONVERT function assumes that the values in column A are in day format and then converts it to the respective minutes with decimal format as needed.
Here, I mark the article as finished. Through this article, I tried to explain various methods to convert the time values to respective decimal values.
The essential arithmetic operation multiplies the time value by 24 hours or 1440 minutes to convert it into respective decimal hour or decimal minute values.
The HOUR, MINUTE, and SECOND functions are specifically designed to work with the time values. Using arithmetic operations, they extract the hours, minutes, and seconds from the time and then convert them to respective decimal values in hours or minutes.
The NUMBERVALUE function converts the time value to decimal number format as the time represents the fraction in which the day is completed. This fraction number value is then converted to an hour decimal value by multiplying the formula output by 24.
The VBA method is unique in its own way. It allows you to dynamically set the range for which you want to convert the time values to respective decimals.
The CONVERT function assumes the time value as a date and converts it into decimal hour values based on its unit conversion compatibilities.
Other Excel articles you may also like:
