How to How to Use HOUR Function in Excel in Excel - Practical Guide

Learn how to use the HOUR function to extract the hour value from a time or datetime in Excel. This tutorial covers syntax, practical examples, and real-world applications like time tracking, scheduling, and data analysis. Master this essential time function to manipulate time data efficiently.

Why This Matters

The HOUR function is critical for time-based analysis, payroll calculations, and scheduling tasks. It allows you to isolate hours from timestamps for reporting and conditional logic.

Prerequisites

•Basic understanding of Excel formulas
•Familiarity with time/datetime data types
•Knowledge of cell referencing

Step-by-Step Instructions

Open Excel and prepare your data

Launch Excel and create or open a spreadsheet containing time values in a column (e.g., Column A with times like 14:30:45 or 2:15:00 PM).

Click the target cell for your formula

Select the cell where you want the hour result to appear (e.g., cell B1).

Type the HOUR formula

Enter the formula =HOUR(A1) to extract the hour from the time value in cell A1. The syntax is =HOUR(serial_number).

Press Enter to execute

Hit Enter to confirm the formula; the hour value (0-23) will display in your selected cell.

Copy the formula down

Click the cell with your formula, then drag the fill handle down to apply HOUR to all rows with time data.

Alternative Methods

Extract hour using TEXT function

Use =TEXT(A1,"h") to format and extract the hour as text, useful when you need formatted output rather than numeric values.

Combine HOUR with other time functions

Pair HOUR with MINUTE and SECOND (e.g., =HOUR(A1)&":"&MINUTE(A1)) to create custom time displays.

Tips & Tricks

✓HOUR returns values between 0 and 23 in 24-hour format; use IF statements to convert to 12-hour AM/PM format if needed.
✓Ensure your time data is properly formatted as time values, not text, for HOUR to work correctly.
✓Combine HOUR with conditional formatting to highlight specific hours (e.g., morning, afternoon, night shifts).
✓Use HOUR in formulas to calculate hourly rates, time differences, or categorize data by time of day.

Pro Tips

★Nest HOUR with SUMIF to calculate total hours worked during specific times of day for payroll analysis.
★Use HOUR in array formulas to filter and analyze time-based data across multiple columns simultaneously.
★Combine HOUR(NOW()) to extract the current hour for dynamic, real-time conditional logic in dashboards.

Troubleshooting

HOUR function returns 0 for all entries

Your time data may be formatted as text. Select the column, go to Data > Text to Columns, and ensure the format is set to Time before applying HOUR.

Formula shows #VALUE! error

Verify the cell reference contains valid time data. Check Home > Format Cells to confirm the cell is formatted as Time, not Text or General.

Results differ from expected hours

Excel stores times as decimal fractions (0-1 represents 0-24 hours). If your data is in text format like "14:30", convert it first using TIMEVALUE() before HOUR.

Related Excel Formulas

MINUTE SECOND TIME TIMEVALUE NOW TODAY

Frequently Asked Questions

What is the HOUR function syntax?

The syntax is =HOUR(serial_number). The serial_number is a time value that can be a cell reference, a time returned by another function, or a time literal (e.g., TIME(14,30,0)).

Can HOUR work with text-formatted times?

No, HOUR requires actual time values (serial numbers). If your data is text, use TIMEVALUE() to convert it first: =HOUR(TIMEVALUE(A1)).

What range of values does HOUR return?

HOUR returns values from 0 to 23, representing hours in 24-hour format. Hour 0 is midnight, hour 12 is noon, and hour 23 is 11 PM.

How do I use HOUR for time-based calculations?

Combine HOUR with IF, SUMIF, or AVERAGEIF to analyze data by hour. For example, =SUMIF with HOUR can total values from specific hours of the day.

Does HOUR ignore minutes and seconds?

Yes, HOUR extracts only the hour component and ignores minutes and seconds entirely, returning only the integer hour value.