Master the HOUR Function: Extract Hours from Time Values in Excel
=HOUR(serial_number)The HOUR function is one of Excel's most straightforward yet powerful date and time utilities, designed specifically to extract the hour component from a time value. Whether you're managing employee schedules, analyzing time-stamped data, or creating business reports, understanding how to use HOUR effectively can significantly streamline your workflow. This function returns an integer between 0 and 23, representing the hour in 24-hour format, making it ideal for time-based calculations and data analysis. In practical business scenarios, the HOUR function proves invaluable when you need to categorize activities by time of day, calculate shift patterns, or analyze peak business hours. For instance, customer service departments use HOUR to identify which hours generate the most support tickets, while retail businesses leverage it to understand shopping patterns throughout the day. The function works seamlessly across all modern Excel versions, from Excel 2007 through Excel 365, ensuring compatibility with virtually any spreadsheet you encounter in professional environments.
Syntax & Parameters
The HOUR function follows a simple syntax structure: =HOUR(serial_number). The serial_number parameter is required and represents the time value from which you want to extract the hour. In Excel, time values are stored as decimal numbers where 1 represents 24 hours. The serial_number can be provided in several ways: as a direct time value like TIME(14,30,0), as a cell reference containing a time, as text in a recognized time format like "2:30:00 PM", or as the result of another function like NOW() or TODAY(). The function returns an integer between 0 (midnight) and 23 (11 PM), using 24-hour format regardless of how the time is displayed in your spreadsheet. When working with the HOUR function, it's important to understand that Excel interprets time as a fraction of a day. If your serial_number is invalid or outside the acceptable time range, Excel will return a #NUM! error. The function automatically ignores any date information in the serial_number and focuses solely on the time component. For maximum clarity in your formulas, always ensure your time values are properly formatted as time data types rather than text strings. This prevents unexpected errors and ensures your calculations remain accurate across different Excel environments and regional settings.
serial_numberPractical Examples
Extracting Hour from Current Time
=HOUR(NOW())This formula uses the NOW() function to get the current date and time, then extracts just the hour component. If the current time is 2:45:30 PM, the formula returns 14.
Categorizing Employee Clock-In Times
=IF(HOUR(A2)>=12,"Afternoon","Morning")This formula extracts the hour from the time in cell A2 and categorizes it. If the hour is 12 or greater, it returns "Afternoon"; otherwise, it returns "Morning".
Analyzing Peak Business Hours
=COUNTIFS(B2:B100,">=9",B2:B100,"<=17")First, create a helper column with =HOUR(A2) for each timestamp. Then use COUNTIFS to count entries where the hour is between 9 and 17 (5 PM in 24-hour format).
Key Takeaways
- HOUR extracts the hour component (0-23) from any time value, using 24-hour format consistently across all Excel versions.
- The function is beginner-friendly with simple syntax =HOUR(serial_number) and works with time values, cell references, text times, and functions like NOW().
- Common errors (#NUM!, #VALUE!, #NAME?) are easily avoided by ensuring proper data formatting and correct function spelling.
- HOUR combines powerfully with IF, COUNTIF, SUMPRODUCT, and other functions to enable sophisticated time-based analysis and categorization.
- The function is universally available across Excel 2007-365, Google Sheets, and LibreOffice, making it a reliable choice for portable spreadsheet solutions.
Pro Tips
Use HOUR with conditional formatting to highlight specific hours. Create a rule with =HOUR($A1)>=14 to highlight all entries from 2 PM onwards, making time-based patterns visually obvious.
Impact : Dramatically improves data visibility and helps identify trends at a glance without manual scanning.
Combine HOUR with COUNTIF to create an hour distribution analysis. Use =COUNTIF(helper_column,14) where helper_column contains =HOUR(time_column) to count occurrences of each hour.
Impact : Enables quick heat-map analysis of when most activities occur, supporting data-driven business decisions.
Remember HOUR uses 24-hour format (0-23). If you need 12-hour format conversion, use =MOD(HOUR(A1),12) for 0-11 range, or add conditional logic for AM/PM designation.
Impact : Prevents confusion when displaying times to users expecting 12-hour format and ensures accurate time categorization.
For international spreadsheets, HOUR ignores regional time format settings and always returns 24-hour format integers. This makes it ideal for data consistency across different locales.
Impact : Ensures formula reliability and consistency regardless of user location or Excel language settings, critical for multi-regional organizations.
Useful Combinations
HOUR with IF for Shift Classification
=IF(HOUR(A1)<12,"Morning",IF(HOUR(A1)<18,"Afternoon","Evening"))This combination extracts the hour and uses nested IF statements to classify times into three shifts: Morning (0-11), Afternoon (12-17), and Evening (18-23). Useful for shift management and labor scheduling.
HOUR with SUMPRODUCT for Hour-Based Aggregation
=SUMPRODUCT((HOUR(A2:A100)=14)*(B2:B100))This formula sums values in column B only for rows where the hour in column A equals 14 (2 PM). Perfect for analyzing sales, transactions, or activities during specific hours of the day.
HOUR with CONCATENATE for Time Logging
=CONCATENATE(HOUR(NOW()),":",MINUTE(NOW()),":",SECOND(NOW()))This combination creates a formatted time string by extracting and concatenating the hour, minute, and second components. Useful for creating timestamp logs or audit trails in your spreadsheet.
Common Errors
Cause: The serial_number parameter contains a value outside the valid time range or is an invalid time representation. For example, using HOUR(25) or HOUR(-1) exceeds the valid 0-23 hour range.
Solution: Verify that your time value is between 0 and 1 (representing 0 to 24 hours). Check that time values are properly formatted. Use TIME() function to create valid time values: =HOUR(TIME(14,30,0)) instead of =HOUR(25).
Cause: The serial_number is text that Excel cannot interpret as a valid time value, or it's a completely non-numeric value like "ABC" or an empty string.
Solution: Ensure your time data is formatted as actual time values, not text. Use TIMEVALUE() to convert text to time: =HOUR(TIMEVALUE(A2)). Verify the cell contains actual time data by checking the cell format is set to Time, not Text.
Cause: The function name is misspelled as =HOURE(), =HOURS(), or similar variations, or the function is not recognized by Excel.
Solution: Verify the correct spelling is =HOUR (without 'S' or 'E'). Check that you're using a compatible Excel version (2007 or later). If using older Excel versions, confirm HOUR is available in your installation.
Troubleshooting Checklist
- 1.Verify the input cell contains actual time data, not text. Check cell format is set to 'Time' not 'Text' by right-clicking and selecting Format Cells.
- 2.Confirm the time value is within valid range (0-1 in decimal, or 00:00:00 to 23:59:59 in time format). Values outside this range will return #NUM! error.
- 3.Check for leading/trailing spaces in text-formatted times using TRIM() function before applying HOUR: =HOUR(TRIM(A1)).
- 4.If combining with other functions, verify parentheses are correctly balanced and function names are spelled correctly (HOUR not HOURS).
- 5.Test with a simple time value first like =HOUR(TIME(14,30,0)) to confirm the function works, then gradually add complexity.
- 6.For imported data, use Data > Text to Columns feature to ensure time values are properly recognized as time data type rather than text.
Edge Cases
Using HOUR on a date-only value without time component
Behavior: Returns 0, representing midnight (00:00:00) since dates without explicit times default to 00:00:00
Solution: If you need to handle date-time combinations, ensure your data includes both components or use IF statements to check for date-only values
This is expected behavior and useful for identifying date-only entries in mixed data
Applying HOUR to a negative time value or value >1
Behavior: Returns #NUM! error because time values must be between 0 and 1 (representing 0 to 24 hours)
Solution: Use MOD function to wrap values: =HOUR(MOD(A1,1)) or validate input data before applying HOUR
This prevents errors when working with calculated durations that might exceed 24 hours
Using HOUR with time stored as text in non-standard format like "14.5" or "2.30pm"
Behavior: May return #VALUE! error if Excel cannot parse the text format, or unexpected results if partially recognized
Solution: Use TIMEVALUE to convert: =HOUR(TIMEVALUE(A1)), or standardize text format to hh:mm:ss before applying HOUR
Always validate imported data and convert non-standard formats before processing
Limitations
- •HOUR only returns the hour component (0-23) and cannot directly calculate total hours from durations exceeding 24 hours. For durations >24 hours, use =INT(A1*24) instead.
- •The function ignores date information entirely, so it cannot distinguish between different days. If you need date-aware hour calculations, combine with DATE functions or create helper columns.
- •HOUR returns an integer and cannot display fractional hours. If you need decimal precision (e.g., 14.5 for 2:30 PM), use =A1*24 to convert the time to decimal hours.
- •The function is time-zone agnostic and uses the local system time. For multi-timezone scenarios, you must manually adjust values using time offset calculations before applying HOUR.
Alternatives
Compatibility
✓ Excel
Since Excel 2007
=HOUR(serial_number) - Works identically across Excel 2007, 2010, 2013, 2016, 2019, and Excel 365✓Google Sheets
=HOUR(time_value) - Identical syntax and functionalityGoogle Sheets interprets time values the same way as Excel. Function works seamlessly with Google Sheets time functions like NOW(), TIMEVALUE(), and TIME().
✓LibreOffice
=HOUR(serial_number) - Fully compatible with LibreOffice Calc