Excel HOUR function

Summary

The Excel HOUR function extracts the hour component from a time or datetime value, returning it as a number between 0 and 23. This function is particularly valuable when working with time data, allowing for the isolation of the hour part for various calculations or for integration with other date and time functions.
Syntax
				
					=HOUR(serial_number)
				
			
  • serial_number: A valid Excel time or datetime from which the hour is to be extracted.
Return value
A number representing the hour part of the time, within a 24-hour range (0 to 23).

How to use

The HOUR function can be applied to any valid Excel time or datetime value to extract just the hour portion. It can be especially useful in combination with other time functions or when parsing datetime values for analysis or scheduling purposes.

Examples

Simple HOUR

Basic Hour Extraction: Extracting the hour from a given time:

				
					=HOUR("22:45")
				
			
This formula returns 22, representing the hour component of the time 10:45 PM.
HOUR and TIME

Adjusting Time to a Specific Minute and Second: Combining HOUR with the TIME function to adjust time:

				
					=TIME(HOUR(A1), 0, 0)
				
			

Assuming A1 contains a time, this formula uses HOUR to extract the hour from A1 and then sets the minutes and seconds to 00:00, effectively rounding the time down to the start of that hour.

HOUR with Date Value
Extracting Hour from a DateTime Value: Extracting the hour component from a date and time combination:
				
					=HOUR(A1)
				
			
If A1 contains a datetime value (e.g., “2021-07-15 14:30”), this formula extracts and returns the hour part of the datetime, which is 14 in this case.

Additional Notes

  • The HOUR function is instrumental in time analysis, especially when you need to separate or manipulate the hour component from a time or datetime value.
  • It works well in combination with other date and time functions such as DATE, TIME, MINUTE, and SECOND, offering flexibility in handling time-related data.
  • Ensure that the input is a recognized Excel time or datetime format to avoid the #VALUE! error.
  • Remember, HOUR works on a 24-hour clock system, so PM times will be represented as hours from 13 to 23.

Related Functions

Excel HOUR function

The Excel HOUR function extracts the hour from a time value, returning a number from 0 to 23, ideal for time data analysis.

Excel TIME function

The Excel TIME function creates a time from hour, minute, and second components, allowing for custom time construction.

Excel SECOND function

The Excel SECOND function extracts the second from a time value, returning a number from 0 to 59, useful for time-based calculations.

Excel MINUTE function

The Excel MINUTE function extracts the minute from a time value, returning a number from 0 to 59, useful for time data analysis.

Content Navigation