What Is HOUR Function In Excel?
The HOUR function in Excel is a powerful tool that allows users to extract the hour component from a particular time value. It can be particularly useful in scenarios where data analysis or calculations require isolating and manipulating the hour portion of a timestamp.
By using Hour function, professionals can easily determine specific hours in a given dataset and perform various operations on them. For example, they can use it to track peak hours of customer activity, calculate working hours for employees, or generate reports based on specific time intervals.
Use this Hour Excel Template to follow along with the examples in this article.
Download Excel TemplateIn the following example, we will illustrate the process of calculating the hour from a given time using the HOUR function in Excel.
To achieve this, the complete formula to be entered should be =HOUR(A2).
Once you have entered each value in the previous step, press the Enter key. As a result, the hour will be displayed as 7 in cell B2.
Key Takeaways
- The HOUR function is typically utilized in programming and data analysis to extract the hour component from a given time or timestamp.
- In Excel, time values play a crucial role in representing fractions of a date. These values are cleverly expressed as decimal numbers, allowing for precise calculations and comparisons.
- Excel errors can occasionally disrupt our meticulously crafted formulas. One such error is known as the #VALUE! Error arises when the provided serial_number fails to meet the criteria for a valid Excel time.
- The #VALUE! error serves as a gentle reminder to double-check our inputs and ensure they align with the expected time format. By adhering to Excel’s guidelines, we can navigate the vast sea of data with confidence and precision.
Syntax
Serial_number – This is a required argument. This argument specifies the time from which we want to extract the hour. It should be noted that the function will return the hour value of the specified time. The hour value will be an integer ranging from 0 to 23, where 0 represents 12:00 AM, and 23 represents 11:00 PM.
The serial_number argument can be provided in the following formats:
- Decimal (serial number) representation of an Excel time.
- Text representation of a time.
- Reference to a cell containing a time.
- Time value obtained from another Excel function or formula.
How To Use Hour Function In Excel? (With Steps)
To effectively utilize the HOUR function in Excel, follow these steps.
#1 – Access From The Excel Ribbon
Choose the empty cell which will contain the result.
Go to the Formulas tab and click it.
Select the Date & Time option from the menu.
Select HOUR from the drop-down menu.
A window called Function Arguments appears.
As the number of arguments, enter the value in the serial_number.
Select OK.
#2 – Enter The Worksheet Manually
Select an empty cell for the output.
Type =HOUR() in the selected cell. Alternatively, type =H and double-click the HOUR function from the list of suggestions shown by Excel.
Press the Enter key.
Examples
Example #1 – Return Hour From Time
In the following example, we will demonstrate how to calculate the hour from a given time using the HOUR function in Excel.
The data is organized in a table as follows:
- Column A contains the Data.
- Column B contains the Result.
To calculate the hour using the HOUR function in Excel, follow these steps:
Step 1: Select the column where you want to enter the formula and calculate the result. For this example, we will use cell B2.
Step 2: Enter the Excel HOUR formula in cell B2.
Step 3: Enter the value of the “serial number” argument as A2, which is the date value in cell A2.
Step 4: The complete formula entered should be =HOUR(A2).
Step 5: After entering each value in the preceding step, press the Enter key. The result in cell B2 will be displayed as “0”.
Step 6: Press enter and drag the cursor to cell B4, as shown in the image below.
Example #2 – Return Hour From Date
In the following example, we will demonstrate how to calculate the hour from a given date using the HOUR function in Excel. The data is organized in a table, with Column A containing the Data and Column B containing the Result.
To calculate the hour using the HOUR function in Excel, follow these steps:
Step 1: Select the column where you want to enter the formula and calculate the result. For this example, we will use cell B2.
Step 2: Enter the Excel HOUR formula in cell B2.
Step 3: Enter the value of the serial number argument as A2, which represents the date value in cell A2.
Step 4: The complete formula entered should be =HOUR(A2).
Step 5: After entering each value in the preceding step, press the Enter key. The result in cell B2 will be displayed as 10.
Step 6: Press enter and drag the cursor to cell B4, as shown in the image below.
Example #3 – Return Hour Ignoring Minutes
In the following example, we will demonstrate how to calculate the hour ignoring minutes using the HOUR function in Excel. The data is organized in a table, with Column A containing the Date and Column B containing the Result.
To calculate the hour using the HOUR function in Excel, please follow these steps:
Step 1: Select the column where you want to enter the formula and calculate the result. For this example, we will use cell B2.
Step 2: Enter the Excel HOUR formula in cell B2.
Step 3: Enter the value of the serial number argument as A2, which represents the date value in cell A2.
Step 4: The complete formula entered should be =HOUR(A2).
Step 5: After entering each value in the preceding step, press the Enter key. The result in cell B2 will be displayed as 20.
Step 6: Press enter and drag the cursor to cell B4, as shown in the image below.
Example #4 – Return Hour Using TIME Function
In the following example, we will demonstrate how to calculate the hour using the Time function using the HOUR function in Excel. The data is organized in a table, with Column A containing the Date and Column B containing the Result.
To calculate the hour using the HOUR function in Excel, please follow these steps:
Step 1: Select the column where you want to enter the formula and calculate the result. For this example, we will use cell B2.
Step 2: Enter the Excel HOUR formula in cell B2.
Step 3: Enter the value of the serial number argument as A2, which represents the date value in cell A2.
Step 4: The complete formula entered should be =HOUR(A2).
Step 5: After entering each value in the preceding step, press the Enter key. We can see the result in cell B2 as 8:40 AM.
Step 6: Press enter and drag the cursor to cell B4, as shown in the image below.
Important Things To Note
- The accurate usage of the HOUR function is with appropriate datatypes.
- The HOUR function is an invaluable tool for professionals looking to gain deeper insights into their data and make informed decisions based on precise time analysis.
- We can combine Excel’s HOUR function with other functions like IF or SUMIF to create more complex formulas that meet specific business requirements.
Frequently Asked Questions (FAQs)
• Firstly, this function returns only the hour portion of a given time value, excluding minutes and seconds.
• Additionally, the HOUR function operates on a 24-hour clock format, which may not align with specific needs or a different time format, such as AM/PM.
In the following example, we will demonstrate the process of calculating the hour from a decimal value using the HOUR function in Excel.
To accomplish this, you need to enter the complete formula as =HOUR(A2).
After entering each value in the previous step, press the Enter key. We can see the hour in cell B2.
One can activate the HOUR function in Excel using the following steps:
• Choose the empty cell which will contain the result.
• Go to the “Formulas” tab and click it.
• Select the “Date & Time” group.
• Select “HOUR” from the drop-down menu.
• A window called “Function Arguments” appears.
• As the number of arguments, enter the value in the “serial_number.”
• Select OK.
Download Template
Use this Hour Excel Template to follow along with the examples in this article.
Download Excel Template