Excel HOUR Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 34 of 105 · Full Excel course

The Excel HOUR function returns the hour component of a time as a whole number from 0 (12:00 AM) to 23 (11:00 PM). It reads the fractional part of a date-time serial number, so it works on times, timestamps and time text such as “2:30 PM”. HOUR is used to group events by hour, classify shifts and convert times into decimal hours for payroll and billing.

Table of Contents

Syntax

=HOUR(serial_number)

Arguments

Argument	Required / Optional	Meaning
serial_number	Required	The time to read: a cell containing a time or date-time, a decimal such as 0.75, a TIME or NOW result, or time text such as “18:30” or “6:30 PM”.

How HOUR works

Excel stores times as fractions of a day: 0.5 is 12:00, 0.75 is 18:00. HOUR multiplies the fraction by 24 and returns the whole-number part, ignoring any date. =HOUR(0.75) returns 18 and =HOUR("2026-09-04 15:30") returns 15. Because only the fraction is used, HOUR wraps at 24: a duration of 26 hours stored as 1.0833 returns 2, not 26. Text that is not a valid time returns #VALUE!, and negative numbers return #NUM!.

Step-by-step example: group orders by hour of the day

Column A holds order timestamps such as 04/09/2026 14:27.
In B2 enter =HOUR(A2) and fill down. Each row shows a number from 0 to 23.
In D2:D25 list the hours 0 to 23 and in E2 enter =COUNTIF($B$2:$B$1000,D2).
Fill E2 down. The table now shows how many orders arrived in each hour, ready for a column chart.

Practical use cases

1. Shift classification

=IF(HOUR(A2)<6,"Night",IF(HOUR(A2)<14,"Morning",IF(HOUR(A2)<22,"Evening","Night")))

2. Decimal hours from a time (for billing)

=HOUR(A2)+MINUTE(A2)/60

Turns 7:45 into 7.75. For durations that may exceed 24 hours use =A2*24 instead.

3. Round a time down to the hour

=TIME(HOUR(A2),0,0)

4. Flag out-of-hours activity

=OR(HOUR(A2)<9,HOUR(A2)>=18)

5. Sales between 12:00 and 13:59

=SUMPRODUCT((HOUR($A$2:$A$500)>=12)*(HOUR($A$2:$A$500)<14)*$C$2:$C$500)

Common mistakes and tips

Durations over 24 hours: HOUR(30:00) returns 6. For total elapsed hours use INT(A2*24) or format the cell as [h]:mm.
Text times: “14.30” with a full stop is not a valid time on most systems. Replace the separator with a colon first.
12-hour text: “2:30 PM” is read correctly and returns 14, but “2:30” alone is read as 02:30.
Result shown as a time: a cell formatted h:mm displays 14 as 00:00. Set the format to General.
Time zone: HOUR reads the stored value; NOW() uses the computer’s local time, so shared workbooks can show different hours.

HOUR compared with TEXT, INT and MOD

HOUR is the simplest way to get the hour as a number you can compare and count. TEXT(A2,”h”) or TEXT(A2,”hh AM/PM”) gives the hour as text, which is right for labels but not for arithmetic. INT(A2*24) gives total hours for durations longer than a day, because it does not discard the date part the way HOUR does. MOD(A2,1) strips the date from a timestamp and leaves the time, which is useful before comparing times from different days. For minutes and seconds the companion functions MINUTE and SECOND follow the same rules, and TIME rebuilds a time from the three components.