HOUR in Google Sheets: Extract Clock Hours and Build Time-of-Day Rules

• Introduced: first introduction date is not established by the official history reviewed; no desktop release number is inferred.
• Availability: documented in the rolling Google Sheets service; these examples use the web worksheet workflow.
• Checked: October 14, 2026; official HOUR reference. Documentation and independent models, with no native Google Sheets execution or wider client certification.

=HOUR(time)

Use a date-time cell, a function returning a date-time value or a suitable numeric serial. Google’s page describes an integer component and notes that a date created without a time has a midnight time portion.

The page includes a quoted-time sample but also warns about automatic conversion. This guide therefore uses numeric TIME and DATE controls rather than claiming every text pattern is accepted. A bare expression such as HOUR(12:00:00) is not a valid way to supply a time literal.

=HOUR(TIME(0,0,0))
=HOUR(TIME(8,45,30))
=HOUR(TIME(12,0,0))
=HOUR(TIME(23,59,59))

Expected results are 0, 8, 12 and 23. A PM display does not turn 23 into the number 11; the component follows the ordinary 24-hour clock interpretation.

Display these outputs as plain numbers. Formatting the result 8 as a date or time would reinterpret the component as an unrelated serial value rather than display the original 08:45:30 timestamp.

=HOUR(DATE(2026,10,14)+TIME(8,45,30))
=HOUR(DATE(2026,10,15)+TIME(8,45,30))
=HOUR(DATE(2026,10,14))

Expected results are 8, 8 and 0. The first two timestamps are a full day apart, but their hour components match. The date-only value in the third control has midnight as its time portion.

Keep the full timestamp when the day matters. A column of hour numbers cannot reconstruct which date an event occurred on.

Enter six numeric TIME controls in A2:A7: 05:59, 06:00, 11:59, 12:00, 17:59 and 18:00, each constructed with TIME. Fill down:

=IF(HOUR(A2)<6,"Early",IF(HOUR(A2)<12,"Morning",IF(HOUR(A2)<18,"Afternoon","Evening")))

Expected labels are Early, Morning, Morning, Afternoon, Afternoon and Evening. The boundaries are explicit: 06:00 begins Morning, 12:00 begins Afternoon and 18:00 begins Evening.

This classification ignores the date and uses chosen reporting labels. It does not infer a shift schedule, opening hours or daylight conditions. If the policy changes to a half-hour boundary, checking only HOUR would no longer be sufficient.

Put =DATE(2026,10,14)+TIME(8,0,0) in D2 and =DATE(2026,10,15)+TIME(10,30,0) in E2.

=HOUR(E2)-HOUR(D2)
=24*(E2-D2)

Expected results are 2 and 26.5. The first subtracts 10−8 and loses both the completed day and the extra thirty minutes. The second measures the full numeric interval.

Even when dates match, HOUR subtraction can discard minutes. Use the whole timestamp difference for an elapsed interval, then choose units deliberately.

For whole-second controls, the rule “exactly on the hour” needs more than HOUR:

=AND(MINUTE(A2)=0,SECOND(A2)=0)

The inputs 08:00:00, 08:00:30, 08:15:00 and 09:00:00 give expected flags TRUE, FALSE, FALSE and TRUE. Their HOUR values are 8, 8, 8 and 9.

This demonstrates why a component can help classify a timestamp without validating all its other parts. Fractional-second boundary behavior remains outside this whole-second fixture.

With A2 containing =DATE(2026,10,14)+TIME(8,45,30):

=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

Expected clock value: 08:45:30, or 31530/86400 of a day. The date has intentionally been discarded. This control reconstructs whole-second components; it does not promise to preserve subsecond precision.

Google’s short reference does not provide every malformed-input error code, historical-serial endpoint or fractional-second conversion rule. Keep those cases separate from the numeric controls here. The meaning of a timestamp also depends on its input source and the spreadsheet’s shared time-zone context; extracting HOUR does not convert it to UTC.

Related guides: TIME, MINUTE, SECOND and NOW.

• Google HOUR reference.
• Editorial testing standards.

Examples use fictional inputs, English function names and comma argument separators. Expected outputs are independently modeled from the cited rules, not native Sheets captures. Check the spreadsheet locale when reproducing punctuation-sensitive formulas.