The hour function in Excel is a useful tool for extracting the hour value from a time value. This function can be useful for formatting time values, calculating time differences, or for creating schedules and calendars. In this article, we will explore the different ways in which the hour function can be used in Excel, and how it can be combined with other functions to achieve more complex tasks.
The basic syntax of the hour function is as follows:
=HOUR(time_value)
The time_value argument is a cell reference or a value that represents a time. This argument can be a time value entered directly into the formula, or it can be a cell reference that contains a time value. For example, if cell A1 contains the time value “12:00 PM,” then the following formula will return the value 12:
=HOUR(A1)
If the time_value argument is a cell reference, it can be a single cell or a range of cells. For example, the following formula will return the hour value for all the time values in the range A1:A5:
=HOUR(A1:A5)
The hour function will return the hour value as a number between 0 and 23, depending on the time value passed to the function. For example, if the time value is “1:00 AM,” the function will return the value 1, and if the time value is “1:00 PM,” the function will return the value 13.
One of the most common uses of the hour function is for formatting time values. For example, if you have a range of cells that contain time values in the 24-hour format (e.g., “12:00 AM,” “12:30 AM,” etc.), you can use the hour function to extract the hour value and format it as a 12-hour time value (e.g., “12:00 AM,” “12:30 AM,” etc.). To do this, you can use the hour function in combination with the text function, as follows:
=TEXT(HOUR(A1),”00″) & “:” & TEXT(MINUTE(A1),”00″) & ” ” & IF(HOUR(A1)<12,”AM”,”PM”)
In this formula, the hour function extracts the hour value from the time value in cell A1, and the text function formats it as a two-digit number (e.g., “01,” “02,” etc.). The minute function extracts the minute value from the time value in cell A1, and the text function formats it as a two-digit number (e.g., “00,” “30,” etc.). The if function checks the hour value and adds “AM” or “PM” to the time value, depending on whether the hour value is less than or equal to 12.
Another common use of the hour function is for calculating the number of hours between two times. For example, if you have two time values in cells A1 and B1, you can use the hour function to calculate the number of hours between these two times. To do this, you can use the following formula:
=HOUR(B1-A1)
This formula calculates the difference between the two time values, and the hour function extracts the hour value from this difference. For example, if cell A1 contains the time value “12:00 PM” and cell B1 contains the time value “1:00 PM,” the formula will return the value 1.