Mastering Time Functions in Google Sheets

Published on

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.