Definition
The HOUR formula in Excel is a function that, as the name suggests, returns the hour of a specified time, ranging from 0 (12:00 AM) to 23 (11:00 PM).
Purpose
The purpose of the HOUR formula is to extract the hour as a number from a specific time value or time text string. It simplifies data analysis tasks by separating the hour component from full date-time values.
Syntax
Here’s the basic syntax of the HOUR formula:
=HOUR(serial_number)
Parameters
The HOUR formula has a single parameter:
- Serial_number: This represents the Excel time from which you want to extract the hour.
Returns
The HOUR formula yields an integer that signifies the hour component of a given time.
Usage notes
Remember, the HOUR formula is particularly beneficial when you need to isolate the hour component from a time for data analysis.
Availability
Rest assured, you can use the HOUR formula in all versions of Excel.
Example #1
Let’s say you want to extract the hour from the time 18:30. You would utilize the HOUR formula like this:
=HOUR("18:30")
The formula will return the number 18, which represents the hour.
Example #2
Assume you have a time in cell A2 and you wish to determine the hour. Here’s the HOUR formula you would use:
=HOUR(A2)
This formula will return the hour component of the time in cell A2.
Example #3
For this example, let’s extract the hour from the current time:
=HOUR(NOW())
This formula will yield the current hour.
Example #4
In our fourth example, let’s calculate the hour from a full date-time string:
=HOUR("2023-12-31 15:30")
This formula will return the number 15, which represents the hour.
Example #5
In our last example, let’s determine the hour from a time given in 12-hour format:
=HOUR("02:30 PM")
This formula will return the number 14, which is the equivalent of 2 PM in a 24-hour format.
Tips and tricks
A handy trick when using the HOUR formula in Excel is to remember that it also works with date-time strings and can convert a 12-hour format into a 24-hour format.
Limitations
The HOUR formula’s key limitation is that it doesn’t recognize text values that aren’t valid times. In such cases, it returns a #VALUE! error.
Common errors and solutions
The most common mistake when using the HOUR formula is inputting non-time values for the serial_number parameter. Ensure that the input values are valid times to avoid a #VALUE! error.
Best Practices
The best practice when using the HOUR formula is always to verify the input times for accuracy.
List of Related functions
HOUR is just one of many date and time functions in Excel. Other related functions include MINUTE, SECOND, NOW, and TIME.
Frequently Used with the formulas
The HOUR formula often pairs with other formulas like NOW, TODAY, MINUTE, and SECOND for more comprehensive time calculations.
Frequently Asked Questions
Q. What happens if the serial_number is not a valid time?
If the serial_number is not a valid time, the HOUR formula will return a #VALUE! error.
Q. Can I use the HOUR formula with date-time strings?
Yes, the HOUR formula can extract the hour from date-time strings.
Q. Can the HOUR formula handle time in 12-hour format?
Yes, the HOUR formula can convert a 12-hour format into a 24-hour format.
Excel’s HOUR formula is a valuable tool for data analysis tasks. With it, you can simplify and accelerate your time-related calculations.
Visit our YouTube channel to learn step-by-step video tutorials