What Is Convert Time To Decimal In Google Sheets?
The Convert Time to Decimal in Google Sheets helps users to convert any valid format time value to its decimal counterpart. By default, the time and date in Google Sheets depends on our login location, as it is a web-based tool.
We can perform the Google Sheets Convert Time to Decimal using functions such as TIME(), TIMEVALUE(), SPLIT(), the basic multiplication formula or by using the Date format in the format option.
For example, we have a time value in cell A2 and we will convert it to decimal value.
Select cell B2, enter the formula =A2*24 and press “Enter”, as shown below.
The output is “12:258”, as shown above.
Table of contents
Key Takeaways
- The Convert TIME to Decimal in Google Sheets takes numeric valid time values as inputs and returns the Time in decimal format.
- We can get the time values in decimal format in the result cells in 2 ways, namely,
- Before applying the formula, set the format as “Format 🡪 Number”, or
- Once the formula is entered and the result is in another time format, select the result cell and change the format using the “Format 🡪 Number” option.
- We can convert the time to decimal format,
- Using the TIME function, if the hour, minute and seconds value are provided.
- Using the TIMEVALUE function, if the time is provided in “hh:mm:ss” format in a cell.
Syntax
MS Excel or Google Sheets does not have an inbuilt Convert Time to Decimal function. However, we can use certain Google Sheets functions, some multiplication formulas and the “format” menu.
How To Convert Time To Decimal In Google Sheets?
We can Convert Time to Decimal In Google Sheets in the following ways, namely,
- Use functions such as TIME(), TIMEVALUE(), SPLIT(), etc.
- Multiplication formulas, to convert time to decimal in terms of hours, such as,
- “=HOUR(time)+(MINUTE(time)/60) + (SECOND(time)/3600)”, or
- “=(cell reference of a time value) * 24”.
- “Format – Number” option from the ribbon.
Examples
We will consider specific examples to Convert Time to Decimal in Google Sheets for the above-mentioned methods.
Example #1 – Using Google Sheets TIME function (HOUR, MINUTE & SECOND)
We will Convert Time to Decimal in Google Sheets using the TIME function for the values provided below in terms of (HOUR, MINUTE & SECOND).
The steps to use the Time() formula to get the decimal time results are as follows:
Step 1: Select cell D2 and enter the formula =time(A2,B2,C2)
Step 2: Press the “Enter” key. The result is “0.907” after adjusting the decimal points, as shown below. [Note: Google Sheets provides an autofill option, we can either choose it or use the drag and drop option.]
Step 3: Drag the formula from cell D2 to D5 using the fill handle. The output is shown in decimal format.
Example #2 – Converting Time to decimals with the SPLIT function
We have some time values given below. We will convert the values to its decimal counterparts using the Split Function.
The steps to use the Split() formula to get the decimal time results are as follows:
Step 1: Select cell B2 & enter the formula =INDEX(SPLIT(A2,”:”),1)+(INDEX(SPLIT(A2,”:”),2)/60), as shown below.
Step 2: Press the “Enter” key. The result is “0.000” after adjusting the decimal points, as shown below.
Step 3: Drag the formula from cell B2 to B6 using the fill handle. The output is shown in decimal format
Example #3 – Using Simple Multiplication with TIMEVALUE
We have some time values given below in different formats. We will convert the values to its decimal counterparts using the TIMEVALUE Function and simple multiplication method.
The steps to use the multiplication and the TIMEVALUE() formula to get the decimal time results are,
Step 1: First, to use simple multiplication method, select cell B2. Enter the formula =A2*24, and press “Enter”, as shown below.
Step 2: Drag the formula from cell B2 to B5 using the fill handle. It is to get the results shown below.
Step 3: Next, to use the TIMEVALUE function, select cell C2. Enter the formula =TIMEVALUE(A2) and press “Enter”, as shown below.
Step 4: Drag the formula from cell C2 to C5 using the fill handle, to get the results shown below.
[Note: We see the difference in the results of multiplication and TIMEVALUE formula. The reason is, the multiplication gives the results in terms of hours and the TIMEVALUE gives the results in completely converted decimal form.]
Step 5: Let us cross check the Column C TIMEVALUE results and convert to hours to see if it matches the Column B values. Therefore, select cell D2, enter the formula =C2*24 and press “Enter”, as shown.
Step 6: Drag the formula from cell D2 to D5 using the fill handle, to get the results shown below.
Now, we can see that the values in Column B and the values in Column D, based on the Column C values, are the same.
Example #4
We will convert the time to decimals using the TIMEVALUE() and the CONCATENATE() function for the time values given below, in different cells as hours, minute and seconds.
The steps to convert time to decimals using the TIMEVALUE and the CONCATENATE functions are,
Step 1: Select cell B2, enter the formula =TIMEVALUE(CONCATENATE(A2,”:”,B2,”:”,C2)) and press the “Enter” key, as show below.
Step 2: Drag the formula from cells D2 to D4 using the fill handle to get the following results.
The CONCATENATE function first combines the values as strings, which in turn becomes the time_string argument value for the TIMEVALUE function.
Example #5
We will convert time to decimal using the format menu on the Goggle Sheets Ribbon for the following data given below. We have two tables with the same values. One with the result cell that is not in the decimal time format and the other result cell in the decimal format.
The steps to convert time to decimal using the “Format – Number” option are as follows:
Step 1: Select cell D2, enter the formula =time(A2,B2,C2) and press “Enter”, as shown below.
Step 2: The result is “1:30:30 PM” in general format and not a decimal value.
Therefore, to convert it to decimal, choose cell D2, select the “Format” tab – click the “Number” option right-arrow – select the “Number” option, as shown below.
The cell time values change to decimal format, as shown below.
Step 3: Now, let us format the cell D6 first. Therefore, choose cell D6 – select the “Format” tab – click the “Number” option right-arrow – select the “Number” option, as shown below.
Step 4: Now, enter the formula =time(A6,B6,C6) and press “Enter”. We will get the output in decimal value.
Important Things To Note
- The “#VALUE!” error occurs when the arguments are non-numeric or alphanumeric.
- The “#NUM!” error when the hour argument negative, i.e., if the given hours are less than 0. It doesn’t occur for a negative value for a minute or a second.
- We get an error message when no arguments are provided in the formula or the values are in a non-valid time format.
Frequently Asked Questions
The Converting Time into Decimals find its use in various fields, namely,
• Accounting – It is useful in accounting because, in the payroll, it is easy to calculate the salaries to be paid in hourly basis.
• Reports & Dashboards – With the help of the decimal format, we can project the time values in a convenient and user-understandable way. We can use Charts to graphically represent the precise values during presentations.
• Measurements – It is useful while measuring using the units such as, length, weight, litre, etc. to understand the time needed to complete a project. Because we do not always have the units measuring exact to a whole number.
We will Convert Time to Decimal in Google Sheets using the other multiplication method, i.e.
“=HOUR(time)+(MINUTE(time)/60) + (SECOND(time)/3600)”
For example, select cell B4, enter the formula =4+(30/60)+(15/3600) and press “Enter”, as shown below.
Where, the time entered is, 4:30:15. 
The TIME and the TIMEVALUE functions in Google Sheets are in the “Insert” tab and are inserted as follows:
• TIME function: Choose an empty cell for the output – select the “Insert” tab – click the “Functions” option right arrow – click the “Date” option right arrow – select the “TIME” function, as shown below.
• TIMEVALUE function: Choose an empty cell for the output – select the “Insert” tab – click the “Functions” option right arrow – click the “Date” option right arrow – select the “TIMEVALUE” function, as shown below.
A few errors we may come across are,
• #NUM! error – It occurs if the hour argument provided is a negative value, which means the supplied hour is less than 0.
• #VALUE! error – It occurs if any of the arguments are non-numeric.
• #NAME! error – It occurs when the entered function name is wrong.
Download Template
This article must help understand the Convert Time to Decimal in Google Sheets formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Convert Time To Decimal In Google Sheets. We learn its syntax and how to convert time to decimals with examples & working template. You can learn more from the following articles. –
UNIQUE Function In Google Sheets
Leave a Reply