Contact
12 programs
Explore the site

HOUR in Zoho Sheet: Time Formulas and Worked Examples

Date and Time Beginner to Intermediate Zoho Sheet

Extract the 0-to-23 hour component from a time or datetime for shift and reporting rules.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked

Quick answer: =HOUR("3:30PM") gives 15, and =HOUR("09:15:40") gives 9. HOUR extracts the clock-hour component 0 through 23; it does not calculate elapsed hours between two timestamps. Zoho documents that dates with no time component are interpreted as midnight for this function.

Official syntax and arguments

=HOUR(time)
Argument Meaning
time A recognized time or datetime value, a serial date-time, or a parseable time text string. Zoho gives "12:30:00" as an example.

Return: an integer in 0–23. For example, midnight is 0, noon is 12, and 11:59 PM is 23. If a date is present with no time, HOUR uses 00:00:00 according to Zoho’s remarks. Avoid treating HOUR as a duration counter: a 26-hour elapsed period would need different arithmetic.

Platform, version, and formula punctuation

This guide documents the current official Zoho Sheet help as checked October 11, 2026; the source does not give a first-supported version or a per-device support matrix. The formulas below follow the semicolon-delimited syntax in the Zoho TIME reference. Zoho’s September 2026 What’s New says that the Windows/macOS beta desktop apps use Excel’s native formula syntax by default for offline files. Check the editor’s formula helper for the right punctuation on your device and locale, rather than assuming that the web, mobile, and desktop beta formula parsers behave identically. Zoho’s workbook locale settings also influence display and parsing of dates and times.

Set up the example workbook

Create a sheet with the following eight observations. Enter column B as genuine time values using a recognized 24-hour Time cell format (not plain text). Enter columns C:E as integers. Format column F as Text before entering the shown times, because this column is intentionally used for text-to-time conversion. A CSV import may infer F as a time; if it does, re-enter F as text or use the literal string examples instead. The attached CSV is a source-data aid, not proof that an import preserves these cell types.

Row A: Activity B: Time value C: Hour D: Minute E: Second F: Text time
2 Morning inbound 09:15:40 9 15 40 09:15:40
3 Midday pickup 12:30:05 12 30 5 12:30:05
4 Afternoon review 16:45:30 16 45 30 16:45:30
5 Evening dispatch 22:05:09 22 5 9 22:05:09
6 Overnight check 00:10:00 0 10 0 00:10:00
7 Shift handoff 14:00:00 14 0 0 14:00:00
8 Day-end closure 23:59:59 23 59 59 23:59:59
9 Early dispatch 06:20:15 6 20 15 06:20:15

Keep B as a time and F as text. To eliminate ambiguous text interpretation when troubleshooting, construct known times with TIME(hours;minutes;seconds); note that actual cells may display 12-hour AM/PM or 24-hour times depending on cell format and locale.

Worked formulas and expected results

  1. Enter =HOUR(B2) in G2, where B2 is 09:15:40. Expected: 9.
  2. Fill G2 down to G9; the expected series is 9, 12, 16, 22, 0, 14, 23, 6.
  3. Test without a table: =HOUR("3:30PM") gives 15, as in Zoho’s reference.
  4. Test the midnight boundary: =HOUR(B6) gives 0 for 00:10:00. This is not an error or a missing value.
Formula Expected value Interpretation
=HOUR(B2) 9 Morning inbound
=HOUR(B4) 16 4 PM on a 24-hour clock
=HOUR(B5) 22 Late-evening dispatch
=HOUR(B8) 23 11 PM hour

Advanced scenario: identify after-hours records

Use HOUR with IF to flag events at or after 18:00 or before 06:00:

=IF(OR(HOUR(B2)>=18;HOUR(B2)<6);"After hours";"Business hours")

For B2=09:15:40 this returns Business hours. For B5=22:05:09 and B6=00:10:00 it returns After hours. The formula handles an after-hours interval that crosses midnight; HOUR(B2)>=18 on its own would miss post-midnight records. This is a clock-time rule only, not a timezone-aware attendance calculation.

Another useful measure is minutes since midnight: =HOUR(B2)*60+MINUTE(B2) gives 555 at 09:15:40. This drops the seconds intentionally.

Errors, edge cases, troubleshooting

  • Clock hour vs total elapsed time: HOUR resets at midnight and ignores days; subtract time serials with date information to measure a long duration.
  • Time stored as text: Confirm your B cells are parsed as time. If a copied value creates #VALUE!, rebuild it with =TIME(9;15;40) and retry HOUR on the result.
  • Ambiguous 3:30: Display conventions may show 03:30 vs 15:30. Use a 24-hour cell format or clear AM/PM notation.
  • #NAME!: Check spelling and quotation marks; an unquoted time-like string may be interpreted as invalid formula tokens.
  • #REF!: Repair moved/deleted references.
  • Date-only data: Zoho states the missing time is 00:00:00, so an answer of 0 can be correct.

Cross-software considerations

Excel and Google Sheets also offer HOUR, but input parsing, source-date serial systems, locale and argument punctuation require separate checking. Microsoft Excel documents comma-style TIME arguments, whereas Zoho’s TIME help currently uses semicolons. Do not use an Excel serial-date example to infer an identical Zoho epoch or hidden timezone conversion. Microsoft’s TIMEVALUE documentation also describes fractions of days, a distinction from HOUR’s integer clock component.

Official reference and verification

Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Zoho Sheet.

  • No genuine Zoho Sheet screenshots are included; this guide does not use mock application images.