How to Use the TIME and HOUR Functions in Google Sheets
=TIME(hour, minute, second) builds a time value and =HOUR(time) returns the hour of a time as an integer. They work as a pair, like DATE and YEAR.
What TIME and HOUR do
The DATE function builds a date from separate parts. TIME does the same for a time of day: hours, minutes and seconds go in, one time value comes out. HOUR goes the other way and extracts the hour. For the current date and time, see NOW.
Syntax
=TIME(hour, minute, second)
=HOUR(time)
Per Google's help pages, TIME takes literal numbers or cell references. All inputs must be numeric, because text or a reference to text returns #VALUE!. Out-of-range values are adjusted without a warning: an hour of 25 produces 1:00 AM. Decimal values are cut off rather than rounded, so an hour of 12.75 is treated as 12.
HOUR needs a cell reference containing a date/time, a function that returns one (such as TIME), or a number. Sheets stores times as numbers, and HOUR does not convert formats the way typing into a cell does: HOUR(12:00:00) returns an error. Dates entered without a time have a time of 0:00:00.
A step-by-step example
=TIME(11, 40, 59)
This returns the time 11:40:59 (shown in the cell's time format). Wrapping it in HOUR extracts the hour:
=HOUR(TIME(11, 40, 59))
This returns 11. Google's help page uses this exact pair as a sample.
Common mistakes
- Passing text to TIME. It returns #VALUE!.
- Typing a time without quotation marks into HOUR. Use a cell reference, a function, or a quoted string like the page's
HOUR("20:49:59")sample. - Expecting rounding. TIME drops decimals.
Try it yourself
Type your formula into the box, starting with =, then press Check.
Exercise 1: Build a time
Write a formula that builds the time 11:40:59 from its three parts.
Exercise 2: Read the hour
Write a formula that returns the hour of the time 11:40:59, built with TIME.
What to learn next
- NOW: the current date and time.
- DATE: build a date from a year, month and day.
- TEXT: format numbers and dates as text.
Syntax and rules in this guide were checked against Google's TIME/HOUR help page. See all guides.