Date & Time
HOUR Function in Excel
Returns the hour of a time value as a number from 0 to 23.
Syntax
- =HOUR(serial_number)
Arguments
- serial_number (required): Time value
Examples
- =HOUR("3:30 PM") - Extract hour - Result: 15
Excel HOUR function documentation
- HOUR is a Date & Time function that returns the hour portion of a time or datetime serial as a number from 0 to 23.
- Syntax: =HOUR(serial_number). Works on time values, datetimes, and results from [NOW](/functions/now/) or [TIME](/functions/time/).
- Use HOUR to bucket shifts, filter night hours, or build time-of-day KPIs without manual parsing.
- Related: [MINUTE](/functions/minute/), [SECOND](/functions/second/), [TIME](/functions/time/), [TEXT](/functions/text/).
HOUR syntax and serial number rules
- serial_number (required): Excel time or datetime — fraction of 24 hours stored as decimal.
- =HOUR("3:30 PM") returns 15 when Excel parses the text as time.
- HOUR ignores the date portion of a datetime — use [INT](/functions/int/) first if you only want time from NOW().
- #VALUE! when the argument is text Excel cannot interpret as time.
Step-by-step: flag after-hours support tickets
- Step 1 — Ticket timestamps in column B as datetime values.
- Step 2 — Helper column: =HOUR(B2).
- Step 3 — After hours: =IF(OR(HOUR(B2)<9, HOUR(B2)>=17), "After hours", "Business hours").
- Step 4 — Copy down and pivot count by hour bucket.
- Step 5 — Pair with [WEEKDAY](/functions/weekday/) to exclude weekends from SLA rules.
HOUR vs TEXT vs manual extraction
- HOUR — returns numeric 0–23 for math and IF logic.
- TEXT(B2,"hh") — formatted text; not ideal for numeric comparisons.
- Power Query — extract hour during import for large datasets.
- Category hub: [Date & Time functions](/categories/date-time/).
Worked examples to copy
- Hour from time: =HOUR("14:45").
- From NOW: =HOUR(NOW()).
- Morning shift: =IF(HOUR(A2)<12,"AM","PM").
- Night premium: =IF(OR(HOUR(A2)>=22, HOUR(A2)<6), rate*1.5, rate).
People also ask
- Does HOUR use 24-hour or 12-hour clock? — Returns 0–23 (24-hour); format display separately.
- HOUR on date-only cell? — Returns 0 because time portion is midnight.
- HOUR vs TIMEVALUE? — TIMEVALUE converts text to serial; HOUR extracts hour from serial.
- HOUR in Google Sheets? — Same syntax: =HOUR(time_value).
Common errors
- Returns 24-hour format
Use cases
- Time analysis
- Shift calculations
- Scheduling
Frequently asked questions
- What is the HOUR function in Excel? HOUR extracts the hour component from a time value, returning a number from 0 (midnight) to 23 (11 PM). It works with time values, datetime values, or text that Excel can interpret as time.
- How do I convert 24-hour format to 12-hour format? HOUR returns 24-hour format (0-23). To convert: =IF(HOUR(A1)>12, HOUR(A1)-12, IF(HOUR(A1)=0, 12, HOUR(A1))) & IF(HOUR(A1)>=12, " PM", " AM"). Or use TEXT: =TEXT(A1, "h:mm AM/PM").
- How do I calculate hours worked between two times? Subtract times and multiply by 24: =(B1-A1)*24. For times crossing midnight, use: =IF(B1<A1, (B1+1-A1)*24, (B1-A1)*24). This handles shifts like 10 PM to 6 AM correctly.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use HOUR
- Time analysis — common Date & Time scenario for HOUR.
- Shift calculations — common Date & Time scenario for HOUR.
- Scheduling — common Date & Time scenario for HOUR.
HOUR in the Date & Time category
- Browse all Date & Time functions at /categories/date-time/ for related formulas.
- HOUR syntax: =HOUR(serial_number)
- serial_number (required): Time value
- Confirm HOUR arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm HOUR arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- MINUTE (/functions/minute/): Returns the minute of a time value as a number from 0 to 59.
- SECOND (/functions/second/): Returns the second of a time value as a number from 0 to 59.
- TIME (/functions/time/): Returns the decimal number for a particular time.
Errors to watch for
- Returns 24-hour format — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter HOUR on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
HOUR worked examples to copy
- =HOUR("3:30 PM") — Extract hour. Expected result: 15.
HOUR reference summary for crawlers and offline review
- HOUR belongs to the Date & Time category in Excel. Returns the hour of a time value as a number from 0 to 23.
- Full syntax: =HOUR(serial_number). Open /functions/hour/ for parameters, FAQs, and related pages.
- Common mistakes: Returns 24-hour format
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.