Home ยป Functions

HOUR Function in Excel – Extract Hour from Time

In this tutorial, we would unlock the HOUR function in excel, its purpose and objective, syntax, and arguments. We would also take some simple examples to better understand the HOUR formula in excel.

Here we go ๐Ÿ™‚

Purpose of HOUR Function in Excel

The =HOUR formula in excel is used to get the hour value in excel from a specified time as an input argument.

This function gives an output as a whole number that ranges between 0 and 23, where ‘0’ represents 12:00 AM and ’23’ represents 11:00 PM.

Syntax and Argument of HOUR Function

=HOUR(serial_number)

The HOUR formula has only one argument viz. serial number.

  • serial_number – In this argument, specify the serial number of the time from which you want to get the hour value.

The input argument may be either serial number of the time or time value itself. The serial number or time value can be either entered manually or put as a cell reference.

If you enter the time value inside the function argument, then it must be put within the double-quotes.

Simple Example to Understand Excel HOUR Formula

Suppose, you want to extract the HOUR value from the time ’03:49:34 PM’, then simply enter this time within double-quotes as an input argument, as shown below:

=HOUR(“03:49:34 PM”)

As a result, you would get the output as 15.

HOUR Function in Excel - Example #1

But why 15, and not 3? The simple answer to this is that excel understands and returns the time in 24-hour format only. The term ‘PM’ mentioned after the time converts it to 15.

Infographic - HOUR Function Formula in Excel

If you do not want to use the term AM/PM, then it is must to write the time in 24-hours format, like this-

=HOUR(“15:49:34”) i.e. without AM/PM.

Do Not Miss These Points

  • The HOUR function also accepts cell reference as its input argument. In such cases, do not put the values in double-quotes, as demonstrated in the image below.
HOUR Function in Excel - Example #2
  • Any text other than time (in 12-hours or 24-hours format) will throw an #VALUE error. For example, writing “12 mid-night” instead of 12:00 AM or “12 noon” instead of 12:00 PM will give the #VALUE excel formula error.
#VALUE Error in HOUR Function in Excel
  • Any input argument time value entered without the term AM/PM is by default considered as a 24-hours format only.
Excel Default Time Format - 24 hours format
  • You may even enter the serial number of time instead of the time (HH:MM:SS). It falls between 0.0 to 0.99999, where the lowest number 0.0 represents 00:00:00 (12 AM) and the highest 0.99999 represents 23:59:59. All other time value serial number falls between these two decimal numbers. See the image below for better understanding.
HOUR Function Using Time Serial Number

Thank You ๐Ÿ™‚

Leave a Comment