Excel Formula calculating the starting date of a given calendar week

Question

Given a table with the following entries:

     A                B       C             D
1
2
3    Calendar Week    Year    Start Date    End Date
4    5                2022    31/01/2022    06/02/200

I want to calculate the week's start and finish dates using the calendar week. A short Google search yielded the following formula that I believe works:

Start Date = DATE(B4;1;7*A4-WEEKDAY(DATE(B4;;-1))-2)

I don't really understand this formula though.

It appears to calculate a date based on the year (B4), the month of January, and the number of days since January 1st using the calendar week. Using some math, the days are equivalent to 7*CW. I don't comprehend math: Based on the day that the previous year came to a close (2021 ends on a Friday = 5), I am calculating a weekday (1 to 7) and then subtracting 2. Someone explain this to me.

Answer 1

The number of days to take into account when determining the start of the calendar week is adjusted by computing the weekday and then deducting a constant.

The first calendar week begins on Sunday, January 2, or Monday, January 3, depending on whether your weeks begin on Sunday (common in the US) or Monday (usual in Europe).

There are various definitions of the "calendar week", see https://en.wikipedia.org/wiki/ISO_week_date. The ISO definition (citing from there):

The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. of January) in it. The following definitions based on properties of this week are mutually equivalent, since the ISO week starts with Monday:

• It is the first week with a majority (4 or more) of its days in January.
• Its first day is the Monday nearest to 1 January.
• It has 4 January in it. Hence the earliest possible first week extends from Monday 29 December (previous Gregorian year) to Sunday 4 January, the latest possible first week extends from Monday 4 January to Sunday 10 January.
• It has the year's first working day in it, if Saturdays, Sundays and 1 January are not working days.

So I first propose this formula:

=DATE(B4;1;7*(A4-1)-WEEKDAY(DATE(B4;1;4))+5)

By adding seven times the length of a calendar week to the first day of the first week of the year, this method can be used to calculate the first day of subsequent calendar weeks.

Calculating the day of January which is the Sunday of the first week is what we need to do for the outer DATE(B4;1;7*(A4-1)...) in the calculation for the day parameter. (Or, in US usage, the beginning of the week). That Sunday is the one before or on January 4.

The weekday of January 4 is returned by WEEKDAY(DATE(B4;1;4)). It's a Tuesday, for instance, and the function yields 3 for B4 = 2022.

In January, we need to remove that weekday value from five to get the Sunday day of that week.

Reasoning: If January 4 falls on a Sunday, that day's WEEKDAY is 1, which can be subtracted from five to get the answer you want, four. (For January Fourth). We must deduct one additional day from five to get the date of the Sunday for each day later in the week that January 4 falls on. In general, the day in January on which the Sunday of the first week falls will be -WEEKDAY(DATE(B4;1;4))+5. (If it's bad, a day in December will be involved.)

For instance, if January 4 falls on a Tuesday in 2022, its WEEKDAY is 3, and we must deduct that from 5 to get 2 for January 2.

Now we add 7 days per week times the calendar week 7*(A4-1), but subtracting 1 since the calendar week is basically a 1-based index and we need the offset here.

Having confirmed the first formula, we note that by using the distributive principle

7*(A4-1)-WEEKDAY(DATE(B4;1;4))+5 is the same as 7*A4-7-WEEKDAY(DATE(B4;1;4))+5 is the same as 7*A4-WEEKDAY(DATE(B4;1;4))-2

So the final formula is

=DATE(B4;1;7*A4-WEEKDAY(DATE(B4;1;4))-2)