Understanding time functions such as TIME, NOW, HOUR, MINUTE, SECOND, and TIMEVALUE is important in Google Sheets. They are useful in many real-life scenarios.

For example, you can use them to extract time components from a datetime, generate a time sequence or timescale, filter based on specific time components, sort data, and apply conditional formatting.

Time functions are very easy to learn, so I’ve included them in a single tutorial. Here you go!

TIME Function in Google Sheets

The purpose of the TIME function is to return a time value based on the provided hour, minute, and second components.

You can use TIME formulas in criteria parts of functions such as COUNTIF, SUMIF, SUMPRODUCT, FILTER, etc.

Syntax:

TIME(hour, minute, second)

Arguments:

• hour – The hour component, ranging from 0 to 23. Values greater than 23 will be divided by 24, and the remainder will be the hour value.
• minute – The minute component, ranging from 0 to 59. Values greater than 59 will be converted to hours and minutes.
• second – The second component, ranging from 0 to 59. Values exceeding 59 will be converted to minutes and seconds.

TIME Function Examples

Here are a few examples of the use of the TIME function in Google Sheets, providing a clear picture of using this function.

Hour Component:

=TIME(14, 0, 0) // returns 14:00:00
=TIME(30, 0, 0) // returns 06:00:00
=TIME(24, 0, 0) // returns 00:00:00
=TIME(0, 0, 0) // returns 00:00:00

Minute Component:

=TIME(0, 50, 0) // returns 00:50:00
=TIME(0, 70, 0) // returns 01:10:00

Second Component:

=TIME(0, 0, 50) // returns 00:00:50
=TIME(0, 0, 70) // returns 00:01:10

You can specify cell references in the TIME formula. For example, =TIME(A1, B1, C1) will return 14:59:01 if A1, B1, and C1 contain the numbers 14, 59, and 1, respectively.

NOW Function in Google Sheets

The purpose of the NOW function in Google Sheets is to return the current date and time. It’s a volatile function, meaning it updates automatically in the cell where it resides.

It refreshes based on the recalculation settings within the File menu (File > Settings > Calculation > Recalculation).

The NOW function is essential for highlighting the current time, creating countdown timers, and filtering based on the current date and time.

Syntax:

NOW()

Enter =NOW() in any cell to get the current datetime.

HOUR Function in Google Sheets

The purpose of the HOUR function in Google Sheets is to return the hour component from the given time.

Syntax:

HOUR(time)

• time – The time from which to extract the hour component.

Examples:

=HOUR("11:40:59") // returns 11
=HOUR(NOW()) // returns the hour component from the current date and time

The formula =HOUR(A1) will return the hour component from the time or date and time in cell A1.

MINUTE Function in Google Sheets

Similar to the HOUR function, the MINUTE function retrieves the minute component of a given time.

Syntax:

MINUTE(time)

• time – The time from which to extract the minute component.

SECOND Function in Google Sheets

The SECOND function retrieves the second component of a given time.

Syntax:

SECOND(time)

• time – The time from which to extract the second component.

TIMEVALUE Function in Google Sheets

The TIMEVALUE function returns a numerical representation of time, reflecting its fraction of the 24-hour day.

Syntax:

TIMEVALUE(time_string)

• time_string – The string that represents the time.

This formula can accept time strings formatted in 24-hour or 12-hour format.

Examples:

=TIMEVALUE("11:10:15") // returns 0.4654513889
=TIMEVALUE("11:10:15 AM") // returns 0.4654513889

Resources

• Payroll Hours Time Calculation in Google Sheets Using Time Functions
• How to Deduct Lunch Break Time From Total Hours in Google Sheets
• Converting Time Duration to Day, Hour, and Minute in Google Sheets
• Google Sheets: The Best Overtime Calculation Formula
• How to Convert Military Time in Google Sheets
• Combine Time In and Time Out into the Same Row in Google Sheets
• How to Check Time Input in a Range of Cells in Google Sheets
• How to Add Hours, Minutes, Seconds to Time in Google Sheets
• How to Increment Time by Minutes or Hours in Google Sheets
• How to Properly Concatenate Start Time with End Time in Google Sheets
• How to Insert a Static Timestamp in Google Sheets
• Create Custom Time Slot Sequences in Google Sheets
• How to Remove Milliseconds from Timestamps in Google Sheets