WEEKNUM vs. ISOWEEKNUM: What's The Difference in Excel?

As well as being a number cruncher, Excel's time and date functions mean the program can also work as a makeshift timekeeper. Let's look at Excel's two functions that calculate a week number based on a given date, and how you can use these to keep track of your tasks and deadlines.

Why You Might Need to Count Week Numbers

Knowing which week of the year a specific date falls into has numerous professional and personal benefits.

For example, it can help you to track the progress of a task, so you can keep tabs on the number of weeks that have elapsed and how many weeks are left. If you run a business, using Excel's week number functions saves you from having to laboriously state the start and end dates of a week when keeping track of deliveries. At home, it can help you to keep an eye on how many weeks you have left to buy Christmas presents or until you go on that elusive vacation.

WEEKNUM vs. ISOWEEKNUM in Excel

Excel has two ways to track week numbers, and it's important you understand their differences before choosing which one to use.

WEEKNUM

The WEEKNUM function counts the week containing January 1 as week 1, and the subsequent weeks begin on a Sunday by default.

Let's go back to 2021 to see this in action. January 1, 2021 was on a Friday, which meant that week 1 consisted of Friday, January 1 and Saturday, January 2. Given the weeks begin on a Sunday by default with the WEEKNUM function, Sunday, January 3 was the start of week 2.

Given that there are 52.14 weeks in a standard calendar year and 52.29 in a leap year, the range for WEEKNUM is usually 1 to 53. However, if a leap year starts on a Saturday (as in 2000 and 2028), the range is 1 to 54.

However, there's a twist. There may be occasions when you don't want the week number to be calculated according to weeks beginning on a Sunday. In that case, you can enter another argument into the formula.

So, the syntax for WEEKNUM is

=WEEKNUM(<em>a</em>,<em>b</em>)

where a is a valid Excel date, and b is an optional argument that determines on which day the week begins, as below:

Argument b	Week start
1 (default) or 17	Sunday
2 or 11	Monday
12	Tuesday
13	Wednesday
14	Thursday
15	Friday
16	Saturday
21	Monday (with the week containing the first Thursday of the year counted as week 1)

So, sticking with 2021, here's what we get when we change value b:

Notice how in column C, week 2 starts on Sunday because I stated value b as 1. In column D, week 2 starts on Monday, as value b is 2. Finally, in column E, week 1 starts on the Monday of the week containing the first Thursday, as value b is 21.

ISOWEEKNUM

Recognizing the complexities of the WEEKNUM function, Microsoft introduced the much more straightforward ISOWEEKNUM to Excel in 2013. This function follows the International Organization for Standardization (ISO) definition of a week, which regards Monday as the first day, and week 1 is the first week of the year containing a Thursday.

ISOWEEKNUM is the same as using WEEKNUM with 21 as the second argument in the parentheses. This overlap occurs because ISOWEEKNUM was introduced to Excel after WEEKNUM.

In 2021, since January 1 was on a Friday, week 1 didn't begin until the following Monday, which was January 4. The range for ISOWEEKNUM is 1 to 54.

Here's the syntax:

=WEEKNUM(<em>a</em>,<em>b</em>)

where a is a valid Excel date.

Let's see it in action.

In 2021, the first Thursday wasn't until January 7, so week 1 didn't begin until Monday, January 4. On the other hand, 2015 started on a Thursday, meaning we went straight into week 1, with week 2 starting the following Monday (January 5).

Summary

In short, WEEKNUM starts week 1 on January 1, with the subsequent weeks beginning on a Sunday, unless you state otherwise. On the other hand, ISOWEEKNUM designates week 1 as the first week containing a Thursday, with the subsequent weeks beginning on a Monday.

Using WEEKNUM and ISOWEEKNUM With Other Functions

The WEEKNUM and ISOWEEKNUM functions can be used independently to simply return a week number for a certain date. However, they can also be used alongside other Excel functions and tools to perform more complex calculations.

The following function combinations work whether you're using WEEKNUM or ISOWEEKNUM. In my examples, I'll use ISOWEEKNUM.

ISOWEEKNUM With SUM and TODAY

In this example, I want to work out the total lengths and the weeks remaining for each task. Because all the dates are in the same year, I know I can use ISOWEEKNUM and WEEKNUM.

First, let's look at the tasks' total lengths. Having entered the data in columns A, B, and C manually, in cell D2 of my formatted Excel table, I will type

=WEEKNUM(<em>a</em>,<em>b</em>)

because I want Excel to work out the week number for the commencement date, and subtract that from the week number for the deadline date. Remember, when using a formatted Excel table, you can refer to the column headers you have typed in row 1.

Once I've typed the formula into cell D2 and pressed Enter, because I've used a formatted table, it will automatically fill the rest of column D.

Now, I need to work out how many weeks I have left to complete each task. So, in cell E2, I'll type

So, in cell E2, I'll type

=ISOWEEKNUM(<em>a</em>)

This is because I want Excel to work out the current week number—hence the TODAY function being embedded within the ISOWEEKNUM function—and subtract that from the deadline week.

Again, when I press Enter, Excel will complete the rest of column E.

Note that the weeks remaining for task 6 is -1, as the deadline has already passed. I could also use Conditional Formatting to make this clearer.

ISOWEEKNUM With SUMIF

Here, having already used the ISOWEEKNUM function in column B to return the week numbers for the dates in column A, I want Excel to tell me the total sales for a given week number.

To do this, I need to use Excel's SUMIF function in cell F2, as I want Excel to sum the totals based on the week number I enter into cell F1. Here's a reminder of the SUMIF syntax:

=WEEKNUM(<em>a</em>,<em>b</em>)

where a is the range of cells I want to evaluate before making the sum, b is the criteria for that evaluation (this can be a value or a cell reference), and c (optional) is the cells to add if different to a.

So, in cell F2, I will type

=ISOWEEKNUM(<em>a</em>)

because I want Excel to evaluate the Week column in the Sales table, see whether it matches the value I place in cell F1, and then add the Sales column for this criterion. Then, I'll type a week number into cell F1, so Excel can perform the calculation.

Now, if I type a different week number into cell F1, the total sales will update accordingly.

---

As well as Excel's week-based functions, the program also has many other time and date functions that you can use to keep on top of your deadlines.

The above is the detailed content of WEEKNUM vs. ISOWEEKNUM: What's The Difference in Excel?. For more information, please follow other related articles on the PHP Chinese website!