HOUR Function - Google Sheets

The HOUR function is a simple date function that extracts just the hour from a time value. If you are working with times but only care about the hours, the HOUR function lets you get rid of everything else. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied.

Purpose

The HOUR function returns the value of the hour from any given unit of time.

Video Explanation

Syntax

=HOUR(time)

Examples

Example 1 – Plain and Simple

Easy examples of the HOUR function.

Formula	Description	Result
`=HOUR("3:31am")`	Extract the hour value from 3:31am	3
`=HOUR("3:31pm")`	Extract the hour value from 3:31pm	15

Tip: Notice that the HOUR function adds twelve hours to denote PM.

Example 2 - Separating the values of a date and time.

Different date values examined using various functions.

Time value	Tickmark	YEAR	MONTH	DAY	HOUR	MINUTE	SECOND	Formatted as number
7/30/2016	1	2016	7	30	0	0	0	42,581.00
7-30-2016	2	2016	7	30	0	0	0	42,581.00
6/1/2017 2:32 PM	3	2017	6	1	14	32	0	42,887.61
July 31, 2016	4	2016	7	31	0	0	0	42,582.00
12:30 PM	5	1899	12	30	12	30	0	0.52
4/11/2017 11:24:14	6	2017	4	11	11	24	14	42,836.48
4/11/2017	7	2017	4	11	0	0	0	42,836.00
	8	1899	12	30	0	0	0
1	9	1899	12	31	0	0	0	1.00
0.25	10	1899	12	30	6	0	0	0.25
43000	11	2017	9	22	0	0	0	43,000.00

1 A random date typed in using slashes.
2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
3 Typed a random date in and added a time. Now notice the hours and minutes being extracted.
4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
5 If you only type in a time, the "zero" date is 12/30/1899.
6 Entered with the NOW function. This will change constantly in the this linked Gooogle Sheet.
7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
8 A blank cell is December 30, 1899. Similar to tickmark 5.
9 Typing a 1 increments the value in number 8 above by 1 day. In other words "1=12/31/1899". It is just a matter of how it is displayed which can be controlled by going to the Format menu and choosing Number.
10 Just a decimal with no whole number increments the value by hours, minutes and seconds instead of days.
11 To get near the present date, you need to start with 43,000 days away from 12/30/1899.

Live examples in Sheets

Go to this spreadsheet for the examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.

HOUR Function – Google Sheets