Technology

Detailed guide on how to use the TIMEVALUE function

Many people struggle with calculating dates and times. Check out our tutorial on how to use the TIMEVALUE function in Google Sheets to make your work easier!

Administrator

25 December, 2023

123

What is the TIMEVALUE function?

The TIMEVALUE function is a function that provides a numerical value for a time in text format in Google Sheets.

You can use the TIMEVALUE function to calculate and convert time. In businesses, this function is often used to calculate employees’ working hours.

The syntax of the TIMEVALUE function:

=TIMEVALUE(time_string)

Where time_string is a string containing the time value.

Some examples of using the TIMEVALUE function:

To convert time to a numerical value, for example 10:00, you enter the formula:

=TIMEVALUE(“10:00”)

Guide on how to use the TIMEVALUE function in Google Sheets

Steps to use the TIMEVALUE function

Step 1: Select the cell where you want to perform the calculation.
Step 2: Type “=TIMEVALUE” to use the function.
Step 3: Enter “14:50” into the function and press Enter, the function will automatically perform the calculation and return the numerical value in the selected cell.
Step 4: The time_string value can be changed with the corresponding time-containing cell.
Step 5: Hover over the top right corner of the cell, drag and drop to copy the formula to other cells.

Example of using the TIMEVALUE function to calculate working hours

Step 1: In the Time column, convert the time to numerical value in the Start work column.
Step 2: Similarly, convert the numerical value in the Hours column back to time.
Step 3: The function will return the result as a numerical value, but don’t worry because it’s a result related to time.

You can view the time format by clicking on the Other format icon, then selecting the Time format.

Step 4: You can calculate the total working hours of an employee per day by subtracting the end time from the start time. Then, choose the Duration format for the cell.
Step 5: For the remaining cells below, hover over the bottom right corner of the first cell, drag and drop to copy the formula.
Step 6: Use the SUM function to calculate the total working hours. The system will provide an accurate working time result.

Some other functions combined with the TIMEVALUE function

Using the NOW function in combination with the TIMEVALUE function

The NOW function is used to get the current time.

You can combine the TIMEVALUE and NOW functions in Google Sheets to get the numerical value of the current time.

You just need to use the following formula: =TIMEVALUE(NOW())

Using the TIME function in combination with the TIMEVALUE function

The TIME function is used to convert hours, minutes, seconds into a time value. Therefore, when you combine the TIME function with TIMEVALUE, the time you enter will be converted into a numerical value.

For example, you enter the formula: =TIMEVALUE(TIME(15;10;20))

The formula TIME(15;10;20) means the time you enter is 15:10:20

What to note when using the TIMEVALUE function in Google Sheets?

When using the TIMEVALUE function in Google Sheets, the time_string value must be enclosed in double quotation marks. It represents time in 12 or 24-hour format.

For example: =TIMEVALUE(“4:15 PM”) or =TIMEVALUE(“16:15”).

The TIMEVALUE function returns a number from 0 to 1. The number 0 corresponds to 12:00:00 AM and 0.9999884259 corresponds to 11:59:59 PM.

Common errors when using the TIMEVALUE function in Google Sheets

Error #NAME?

Most people make this mistake when using TIMEVALUE. If the result displays #NAME, it means you have entered the wrong function name.

To fix this error, simply check the function name you entered and choose the suggested function to avoid naming mistakes in the future.

Error #ERROR!

The #ERROR error means that the time_string value is not enclosed in double quotation marks. In this case, simply add double quotation marks correctly to the formula.

Error #VALUE!

If this error appears on the screen, it means that the time_string value you entered is not a time value. You need to check this value and change it to a time value.

Conclusion

Above is how to use the TIMEVALUE function in Google Sheets, notes when using the function, and common errors. You can leave a comment for us to answer your questions quickly. Hopefully, the useful knowledge above can help you save time and effort when using the software. Wish you successful implementation!