Cellwise Start free

Dates function

Excel HOUR function

HOUR returns the hour of a time as a whole number from 0 (midnight) to 23 (11 PM). It reads the clock, so it works on a time like 1:17 PM, a date-time, or the difference between two times. Use it to bucket timestamps by hour or split a day into shifts.

=HOUR(serial_number)

Arguments

serial_number
A time, a date-time, a cell holding one, or a time in quotes such as "13:17".

HOUR examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1TicketOpenedClosed
2T-10018:429:15
3T-10029:0511:40
4T-10039:5810:20
5T-100413:1716:02
6T-100514:3014:55
7T-100616:4817:30

Download this sheet as an Excel practice file (.xlsx)

  1. =HOUR(B2)

    Result: 8

    T-1001 was opened at 8:42, so the hour is 8. Minutes are dropped, not rounded.

  2. =SUMPRODUCT(--(HOUR(B2:B7)=9))

    Result: 2

    Two tickets came in during the 9 o’clock hour. COUNTIF cannot apply HOUR to each cell, so SUMPRODUCT does the counting.

  3. =IF(HOUR(B5)<12,"Morning","Afternoon")

    Result: Afternoon

    HOUR(13:17) is 13, so T-1004 is an afternoon ticket. Change 12 to split the day at a different hour.

  4. =HOUR(C3-B3)

    Result: 2

    T-1002 took 2 hours and 35 minutes, so HOUR of the difference is 2. For decimal hours use =(C3-B3)*24 instead.

How to use HOUR

  1. Click the cell for the result.
  2. Type =HOUR( and click the cell with the time.
  3. Close the bracket and press Enter.
  4. Fill it down, then count or sum by the hour column with COUNTIF or a pivot table.

Common HOUR mistakes

Using it on a duration over 24 hours

HOUR only returns 0 to 23. A total of 30 hours is stored as 1.25 days, and HOUR(1.25) is 6. Multiply by 24 for total hours, or format the total with [h]:mm.

The time is text

An imported "13:17 " with a trailing space may be text. HOUR accepts time-like text, but sorting and math may not. Convert it with TIMEVALUE or Data > Text to Columns.

Expecting a rounded hour

8:59 returns 8. To round to the nearest hour use =HOUR(MROUND(B2,"1:00")).

HOUR FAQ

How do I extract the hour from a date and time in Excel?

Use =HOUR(A2). It ignores the date part and returns 0 to 23. For a 12-hour label use =TEXT(A2,"h AM/PM") instead.

How do I count entries by hour in Excel?

Add a helper column with =HOUR(B2) and use COUNTIF on it, or count without a helper: =SUMPRODUCT(--(HOUR(B2:B100)=9)).

How do I convert a time to decimal hours?

Multiply by 24: 2:30 becomes 2.5. HOUR only gives the whole hours.

Guides that use HOUR

Related functions

Updated