ExcelInsider Team
Whether working with time data in Excel for project management, payroll, billing, or timesheets, it’s sometimes required to convert hours and minutes into decimal format. This is particularly helpful when figuring out overall expenses or work hours using hourly rates. The time can be formatted in various ways which requires different methods to convert hours and minutes to decimal.
Steps to convert hours and minutes to decimal using the HOUR and MINUTE functions:
➤ Create a separate column. Select the cells below the heading in it and right click on it.
➤ Go to Format Cells > Number > 1234.10 option and set the Decimal Value to 2. Press OK.
➤ Click on cell C2 and insert the formula: =HOUR(B2) + MINUTE(B2)/60.
➤ Click and drag the Fill handle down the entire cell range for applying the formula to the whole column.
To make computations and analyses easier, we’ll walk you through the various methods in this guide for converting time numbers like 2:30 or 2 hours and 30 minutes into decimal counterparts like 2.5. We have included solutions to both hh:mm and text string time formats.
Use Basic Arithmetic Formula to Convert Hours and Minutes to Decimal
If your time is entered in hh:mm format (e.g., 2:30), Excel stores it as a fraction of a 24-hour day. To convert this to a decimal hour, you need to simply multiply the time by 24. So, we will use a simple arithmetic formula to do this.
Suppose, this is our dataset where we need to transform the hours and minutes in the time column to decimal values for better calculation.
Follow these instructions to convert these hh:mm format time into decimal values:
➤ Create a separate column C for the Decimal Values where we will apply the formulas.
➤ We have to make sure the cell with the formula is formatted as Number to see the decimal result. So, select cell range C2:C8 and right click to see the options.
➤ Go to Format Cells.
➤ Select Number > 1234.10 option. Set the Decimal Value to 2. Then press OK.
Now, column C will be formatted as Number as we require.
➤ Click on cell C2 and put the formula:
=B2*24
➤ Click and drag the Fill handle to the cell range C3:C8 to apply the formula to the entire column C.
➤ Now, all the hours and minutes of column B will be converted into decimal values in column C.
Apply HOUR and MINUTE Functions to Convert Hours and MInutes into Decimals
This is another method to use when the time in the dataset is in hh:mm format. The HOUR and MINUTE functions let you extract components from a time value and calculate the decimal manually.
To apply both these functions for converting hours and minutes into decimals, follow these steps:
➤ Create a separate column C for the Decimal Values where we will apply the formulas.
➤ We have to make sure the cell with the formula is formatted as Number to see the decimal result. So, select cell range C2:C8 and right click to see the options.
➤ Go to Format Cells.
➤ Select Number > 1234.10 option. Set the Decimal Value to 2. Then press OK.
Now, column C will be formatted as Number as we require.
➤ Click on cell C2 and put the formula:
=HOUR(B2) + MINUTE(B2)/60
Here, HOUR(B2) gives the whole hour before the decimal point. The MINUTE(B2)/60 converts minutes to a decimal fraction.
➤ Click and drag the Fill handle to the cell range C3:C8 to apply the formula to the entire column C.
➤ All the hours and minutes are now converted into their decimal values in column C.
Use a Custom Formula to Convert Text Based Hours and Minutes to Decimals
If the time is entered as a text string, Excel won’t recognize it as a time. You’ll need to parse it manually with the help of a unique formula. This method is used when the time is entered as x hours y minutes– such text format.
Suppose, the hours and minutes are presented in the dataset like this instead of the hh:mm time format:
Now, to convert these text strings to decimal values, we need to follow these instructions:
➤ Create a new column for Decimal Values and make sure the cells in this column are formatted to Number.
➤ In cell C2, copy and paste this formula:
=IFERROR(VALUE(LEFT(B2,FIND("hour",B2)-1)),0)+IFERROR(VALUE(MID(B2,FIND("hour",B2)+6,FIND("minute",B2)-FIND("hour",B2)-6))/60,0)
Here, the LEFT(B2,FIND(“hour”,B2)-1) part extracts the number before “hour” and the MID(…) function extracts the number between “hour” and “minute”.
➤ Click and drag the Fill handle to the cell range C3:C8 to apply the formula to the entire column C.
➤ The text based hours and minutes will be converted into decimal values now.
Frequently Asked Questions
What’s The Easiest Way to Convert a Time Like 2:30 to Decimal?
The easiest way to convert a time which is in hh:mm format like 2:30 is a simple formula. Multiply the value by 24 as it is shown as a fraction of 24 in Excel. For example, assuming your time is in cell B2, put in the formula- =B2*24 to convert it into decimal value.
What’s The Difference Between Excel Time and Decimal Hours?
Excel stores time as a fraction of a day. For instance, 6:00 AM = 0.25, since it’s 1/4 of 24 hours. Decimal hours represent time in base-10 format. So, 6.5 hours in decimal will actually be 6 hours 30 minutes . Converting between the two requires multiplication or division by 24.
Can Excel Directly Convert 7 hours 30 minutes Time Format With A Function?
No, Excel does not have one built-in function to directly transform text strings such as 7 hours 30 minutes into decimal. You need to combine several functions LEFT(), MID(), FIND() and VALUE() to convert such values into decimals.
Wrapping Up
For precise and effective time-based computations, Excel’s decimal conversion feature for hours and minutes is crucial. We have covered several methods in this tutorial that were tailored to different data formats.
You can get exact decimal results for typical time values in hh:mm format like 2:30 by multiplying by 24 or by using the HOUR() and MINUTE() methods. We used string operations like LEFT(), MID(), FIND() and VALUE() to extract and convert components from text strings like “8 hours 30 minutes”. Choose the method that suits your data input type and improve your time based reporting in no time!
