Purpose
Return value
Syntax
=HOUR(serial_number)
- serial_number - A valid Excel time.
Using the HOUR function
The HOUR function returns the hour portion of a time as a number between 0-23. For example, with a time of 9:00 AM, HOUR will return 9. HOUR takes just one argument , serial_number , which must be a valid Excel date or a valid Excel time .
Times can be supplied to the HOUR function as text (e.g. “7:45 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM). To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function .
The HOUR function will “reset” to 0 every 24 hours (like a clock). To work with hour values larger than 24, use a formula to convert time to decimal hours .
Example #1 - Hour from time
The HOUR function returns the hour from of a time or date as a number between 0-23. For example:
=HOUR("9:00 AM") // returns 9
=HOUR("9:00 PM") // returns 21
Example #2 - Minutes ignored
The HOUR function ignores minutes and seconds. For example, when given the time “6:30 PM”, HOUR returns 18:
=HOUR("6:30 PM") // returns 18
Example #3 - Hour from date
Some Excel dates include time. When given a date that includes time, the HOUR function will extract the hour and ignore the date. For example, with 29-May-2021 6:00 AM in cell A1:
=HOUR(A1) // returns 6
The date portion of the value is ignored completely. If the date contains no time value, HOUR returns 0 (zero) which is midnight.
Example #4 - with TIME function
You can use the HOUR function to extract the hour and feed the result into another formula, like the TIME function. For example, with the time “8:00 AM” in A1, you could force the time to be on the half-hour with:
=TIME(HOUR(A1),30,0) // returns 8:30 AM
Note: Excel stores dates and times as serial numbers. For example, the date Jan 1, 2000 12:00 PM is equal to the serial number 32526.5 in Excel. To check that Excel is correctly recognizing a date or time, you can temporarily format the date as a number.
Notes
- HOUR returns #VALUE! if serial_number is not recognized as a valid date or time.
- HOUR returns #NUM! if serial_number is out of range.
Purpose
Return value
Syntax
=ISOWEEKNUM(date)
- date - A valid Excel date in serial number format.
Using the ISOWEEKNUM function
The ISOWEEKNUM function returns a week number based on ISO standards. Under this standard, weeks begin on Monday and the week number 1 is assigned to the first week in a year that contains a Thursday, following ISO 8601 .
ISOWEEKNUM takes just one argument, date , which must be a valid Excel date.
Examples
In the example shown, the formula in D5, copied down, is:
=WEEKNUM(B5) // default week number
The formula in E5, copied down the table, is:
=ISOWEEKNUM(C5) // ISO week number
By default the standard WEEKNUM function will start week number 1 on the first day of the year, then increment week numbers on Sundays after that. The ISOWEEKNUM function increments on Mondays, and starts week 1 on the first week that contains a Thursday.
The WEEKNUM function can also be configured to output an ISO week number, by setting the return_type argument to 21. The formula below will output the same week numbers seen in column E of the example:
=WEEKNUM(B5,21) // ISO week number
Notes
- ISOWEEKNUM returns #VALUE! if date is not recognized as a valid date.
- ISOWEEKNUM returns #NUM! if date is out of range.
