Excel Formula calculating the starting date of a given calendar week

0 votes

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.

Dec 9, 2022 in Others by Kithuzzz
• 38,000 points
658 views

1 answer to this question.

0 votes

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)
answered Dec 10, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,600 points
831 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
774 views
0 votes
1 answer

Get number of columns of a particular row in given excel using Java

Use: int noOfColumns = sh.getRow(0).getPhysicalNumberOfCells(); Or int noOfColumns = sh.getRow(0).getLastCellNum(); There ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
3,388 views
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,600 points
1,135 views
0 votes
1 answer

Date difference: different results in Excel vs. Python

To calculate the date difference, we can ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,128 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,600 points
1,008 views
0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,600 points
1,922 views
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,600 points
781 views
0 votes
1 answer

Calculating the week number of a year in Excel

Here is my excel formula. =IF(AND(A1>=DATE(YEAR(A1),1,1),A1< ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,600 points
486 views
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,600 points
793 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP