HOUR function

1 year ago

3 minutes

The HOUR function in Excel extracts the hour from a given time value. It returns the hour as an integer between 0 and 23, representing the hour of the day in a 24-hour format.

Syntax

HOUR(serial_number)

Parameters

serial_number: This is the time value or date-time value from which you want to extract the hour. It can be a time in a cell, a reference to a cell containing a time, or a time value in the format "hh:mm:ss".

How It Works

The HOUR function extracts the hour from a given time or date-time value. Excel stores dates and times as serial numbers, where the integer part represents the date and the decimal part represents the time. The HOUR function works with the decimal part to return the hour portion.
It works in a 24-hour format, where 0 represents midnight (12:00 AM), 12 represents noon (12:00 PM), and 23 represents 11:00 PM.

Examples

Extracting the Hour from a Time: If you have the time 12:45:30 in cell A1, and you want to extract the hour, you can use:
```
=HOUR(A1)
```
Result: 12 (since the hour is 12 in the given time).
Using a Time in a Formula: If you want to extract the hour from the time 15:30 (3:30 PM):
```
=HOUR("15:30")
```
Result: 15 (since the hour is 15 in 24-hour format).
Using the NOW() Function: If you want to extract the hour from the current time:
```
=HOUR(NOW())
```
This will return the hour of the current time.
Using with a Date-Time Value: If you have a date-time value in cell A1, such as 2025-05-01 18:45:00, and you want to extract the hour portion:
```
=HOUR(A1)
```
Result: 18 (since the time is 18:45, which is 6:45 PM).

Common Use Cases

Time Calculations: The HOUR function is commonly used to extract the hour from a time or date-time value when performing time-based calculations.
Work Shifts: It’s useful for calculating or categorizing work hours, such as checking if a time falls within a specific shift or calculating hours worked.
Time-based Conditions: You can use the HOUR function in conditional formulas (e.g., with IF statements) to apply logic based on specific hours of the day.

Important Notes

The HOUR function only returns the hour portion of a time or date-time value. It ignores the minutes, seconds, and any other portion of the date-time value.
The HOUR function works in a 24-hour format. If the time is 12:00 PM, it returns 12; if the time is 1:00 AM, it returns 1, and so on.

Error Handling

If the input is not a valid time or date-time value, the HOUR function will return a #VALUE! error. To handle such cases, you can use IFERROR:

=IFERROR(HOUR(A1), "Invalid Time")

This will return “Invalid Time” if the value in cell A1 is not a valid time.

Summary

The HOUR function in Excel is a simple and effective way to extract the hour portion from a time or date-time value. It returns the hour in a 24-hour format and is commonly used in time-based calculations, work shift analysis, and conditional logic based on hours.

Post Views: 13