The ISOWEEKNUM function returns the week number of the year based on the given date value in Excel.
ISOWEEKNUM Function: A Brief
The ISOWEEKNUM function in Excel inputs a date and returns the week number (from 1 to 54) based on ISO standards. In this system the weeks start on Monday, and week 1 is the first week of the year that includes a Thursday. You can find more information about ISO WEEK DATE here on the official wikipedia page: ISO Week Date.
Function Compatibility:
The ISOWEEKNUM function was introduced in 2013. All Excel versions released after 2013 support this function.
|
Objective |
Value Returned by function |
|---|---|
|
Aim to return the ISO Number from input date. |
ISOWEEKNUM Function will return a ISO numeric values from the input date values. |
ISOWEEKNUM Function: A Syntax
=ISOWEEKNUM(date_value)
- date_value: A valid excel date value in serial number or proper date format.
The ISOWEEKNUM function returns the week number from the input date based on ISO standards. According to this ISO standard the weeks start on Monday, and week 1 is given to the first week of the same year that includes a Thursday.
Difference B/W WEEKNUM & ISOWEEKNUM Function in Excel:
|
Function |
Description |
System Followed |
|---|---|---|
|
WEEKNUM Function |
This function calculates the week number starting from January 1 (week 1), and the week can start on Sunday or Monday based on return type. |
Follows the U.S. system. |
|
ISOWEEKNUM Function |
This function calculates week numbers based on the ISO standard, where the week starts on Monday. |
Follows the ISO 8601 standard and week 1 is the week with the first Thursday of the year. |
Basic Example of Using ISOWEEKNUM Function:
If you’re working in an office where employee’s reports need to be submit weekly and you want to calculate the ISO Week number for each submission date to group and analyze weekly reports. Note: week starting on Monday, according to the ISO 8601 standard.
|
Input Date |
ISOWEEKNUM Result |
Formula Used |
|---|---|---|
|
01-Jan-2024 |
1 |
=ISOWEEKNUM(B4) |
|
15-Jan-2024 |
3 |
=ISOWEEKNUM(B5) |
|
25-Mar-2024 |
13 |
=ISOWEEKNUM(B6) |
|
30-Jun-2024 |
26 |
=ISOWEEKNUM(B7) |
|
02-Sep-2024 |
36 |
=ISOWEEKNUM(B8) |
|
31-Dec-2024 |
1 |
=ISOWEEKNUM(B9) |
Example of using ISOWEEKNUM Function with other Nested Functions:
The below formula is used to identify the cell value contains a valid Excel date value or not.
=IF(AND(ISNUMBER(B4),B4<>""), ISOWEEKNUM(B4), "Invalid or Empty")
From the above image, you can see that the ISOWEEKNUM Function:
- Is returning the ISO week number for a valid input Excel date value.
- Is returning the “Invalid or Empty” for both the “Empty cell value” & “Text value.”
That’s it. This tutorial is originally published on How to Use ISOWEEKNUM Function in Excel?
