Understanding time functions is crucial when it comes to handling salary, payroll, and other time-related calculations. Just like date-related functions, time is an essential factor in these calculations. In this guide, I’ll explain how you can use all the different time formulas in Google Sheets with ease.
If you’re not familiar with date functions in Google Sheets, don’t worry. You can check out our comprehensive guide on Google Sheets Date Formulas to get up to speed.
Time Functions in Google Sheets
When it comes to time formulas, Google Sheets offers seven functions that are easy to use and understand. Let’s dive into each one of them!
TIME Function in Google Sheets
The TIME function in Google Sheets allows you to convert provided hours, minutes, and seconds into a valid time format. To use this function, follow this syntax: TIME(hour, minute, second).
For example, you can enter the hour, minute, and second components in different cells and combine them into a time value using the TIME function. Here’s the formula: =TIME(A2, B2, C2). Alternatively, you can directly input the values like this: =TIME(10, 35, 59).
NOW Function in Google Sheets
The NOW function in Google Sheets returns the current date and time. It’s important to note that this function is volatile, which means it updates automatically whenever the spreadsheet is edited. To use the NOW function, simply enter =NOW() in any cell.
If you want to extract the current time without the date, there are two options you can choose from. The first option is to use a time formula in conjunction with the Format menu. Apply the formula =NOW()-TODAY() in a cell, keep it active, and then go to Format > Number. Select the “Time” format to display the current time.
Alternatively, you can use the TEXT function with NOW() to get the current time without the date. The formula =TEXT(NOW(), "hh:mm:ss") will do the trick.
HOUR Function in Google Sheets
The HOUR function in Google Sheets allows you to extract the hour component from a given time value. To use this function, follow the syntax: HOUR(time). For example, you can use the formula =HOUR("11:40:59") or =HOUR(TIME(A2, B2, C2)).
MINUTE Function in Google Sheets
Similar to the HOUR function, the MINUTE function returns the minute component of a given time. You can use the MINUTE function in Google Sheets by following this syntax: MINUTE(time).
SECOND Function in Google Sheets
Just like the HOUR and MINUTE functions, the SECOND function returns the seconds component of a given time. Use the following syntax: SECOND(time).
TIMEVALUE Function in Google Sheets
The TIMEVALUE function in Google Sheets returns the fractional representation of a time value within a 24-hour day. This function is useful for various time-related calculations. To use the TIMEVALUE function, follow the syntax: TIMEVALUE(time_string).
You can input the time in either 24-hour or 12-hour format. For example, =TIMEVALUE("11:10:15") or =TIMEVALUE("11:10:15 AM"). The result will be a numeric value that you can use for different time-related operations. To convert this numeric value back to time format, use the TEXT formula: =TEXT(E24, "hh:mm:ss").
TO_PURE_NUMBER Function in Google Sheets
The TO_PURE_NUMBER function is a parser function that converts a formatted numeric value, such as a date/time or percentage, into a pure number without formatting. Use the following syntax: TO_PURE_NUMBER(value).
For example, if you have a cell (D2) that contains the time value 11:10:15, you can use the formula =TO_PURE_NUMBER(D2) to get a pure numeric representation of the time. The result will be “0.4654513889”, which is similar to the result of the TIMEVALUE function. The difference lies in the fact that the TIMEVALUE function uses a string element in the formula, whereas the TO_PURE_NUMBER function uses a direct time reference.
These are the main time formulas in Google Sheets that can help you handle various time-related calculations with ease.
Related Time Formula Examples
- Convert Time Duration to Day, Hour, Minute in Google Sheets
- How to Deduct Lunch Break Time From Total Hours in Google Sheets
- How to Format Date, Time, and Number in Google Sheets Query
- Payroll Hours Time Calculation in Google Sheets Using Time Functions
- Google Sheets: The Best Overtime Calculation Formula
- How to Convert Military Time in Google Sheets
- Elapsed Days and Time Between Two Dates in Google Sheets
- Create a Countdown Timer Using Built-in Functions in Google Sheets
- How to Add Hours, Minutes, Seconds to Time in Google Sheets
- How to Format Time to Millisecond Format in Google Sheets
Mastering these time formulas will make your Google Sheets experience smoother and more efficient. Stay tuned for more tips and tricks from Crawlan.com!
In case you encounter any errors while working with time values, you can use the handy TO_PURE_NUMBER function to convert and return a pure number without formatting.
So there you have it! Now you’re equipped with all the knowledge you need to become a Google Sheets time formula master. Happy calculating!
