More:
This version allows entering a specific year and week number and getting the start and end dates of that week.
It has covered the first week issue mentioned above, but not the 'last week' issue.
The larger lookup table (on the right) calculates the weeknum for the first eight days of the year entered in column A, and the day number of the last day of week 1 in column B.
First Week:
Columns A and B are Header columns.
Column A: Entered data.
Enter the year numbers of the years for which you want to be able to calculate the start and end dates. The years do not need to be consecutive or sorted.
Column B: Calculated data.
B2: MATCH(1,C2:J2,1)
Filled down the rest of column B.
MATCH searches for the value 1 in the range C2 to J2 and returns the position in the list of the 'first' occurrence of the search value found. The third argument (1) tells match to accept the 'largest value less than or equal to' the search value. Its use here is to force MATCH to start its search at the 'bottom' (right end) of the list and move toward the top (left side) ensuring the the first acceptable value is the 1 furthest right in the list, corresponding to the last day of week 1.
C2: WEEKNUM(DATE($A2,1,1+COLUMN()−3),2)
Filled right to J2, then all filled down to the bottom of the table.
DATE($A2,1,1+COLUMN()−3),
DATE uses the year in this row of column A, the month number (1) and the day number (1 plus three less than the number of 'this column') to construct the dates of the first eight days of the year. The result is passed to WEEKNUM, which returns the weeknumber for that date.
Main
Columns A and B of Main contain entered data; the number of the year in each row of column A, and the desired week number in column B.
D2: IF(OR(LEN(A2)<1,LEN(B2)<1),"",DATE(A2,1,VLOOKUP(A2,First week::A:B,2,FALSE))+7×(B2−1))
The core part of the formula, shown in bold, gets the year value in column A, searches for it in the first column (A) of the lookup table (columns A and B of First week), and on finding it, returns the value in the second column of that table. FALSE refers to acceptance of a 'close match' (same definition as MATCH's "find largest"). Set to FALSE, VLOOKUP will accept only an exact match for the year, and will return an error message if that cannot be found.
The returned value, the day number of the last day of week 1, is handed to DATE which uses the year value from A2, the month number (1) and the day number returned by VLOOKUP to construct the date of the last day of week 1 of that year.
The last part, +7*(B2-1) multiplies one less than the desired week by the number of days in a week, then adds the result to the date returned by VLOOKUP to get the end date of the desired week.
C2: IF(OR(LEN(A2)<1,LEN(B2)<1),"",MAX(DATE(A2,1,1),D2−6))
The core part of this formula, shown in bold, returns the greater of January 1 of the year specified or the date six days before the value in D2. see also note below)
Both formulas also contain a 'switch' (shown in normal type) which checks for content in this row of columns A and B by 'measuring' the length of the entry on each of the cells in number of characters. If either cell contains less than one character, IF returns a null string, leaving th cell appearing 'blank', and exits.
Note: The end of December issue:
Just as MAX provides a low limit (Jan 1) on the start date of Week 1, MIN can do the opposite for the last day of week 53. Make this small edit to the formula in D2 (and filled down):
original: D2:
IF(OR(LEN(A2)<1,LEN(B2)<1),"", DATE(A2,1,VLOOKUP(A2,First week::A:B,2,FALSE))+7×(B2−1))
revised:D2:
IF(OR(LEN(A2)<1,LEN(B2)<1),"",MIN(DATE(A2,12,31),DATE(A2,1,VLOOKUP(A2,First week::A:B,2,FALSE))+7×(B2−1)))
Spaces inserted in original to align identical parts. Revisions shown in bold.
Regards,
Barry
