Reversed weeknumber function?

[Jaymond Flurrie]

I have like this:
Source Date: December 31st, 2020
Result: 53/2020
Formula: WEEKNUM(A1;21)&"/"&YEAR(A1)

But how about the same backwards, what would you suggest? The exact date would preferably be the particular Monday.

So input: 53/2020 (which can be parsed obviously to 53 and 2020 with a split at the slash)
should somehow calculate 44193 (which is December 28th 2020).

I was thinking of making simply a large lookup table, but I have a feeling that's unnecessary.

Oh and I need to do this without VBA, just by using Excel functions.

 

[6StringJazzer]

I have not done comprehensive testing on this but it works on your example. Please check to see if this works for your other data. The "ww/yyyy" value is in B1 for this example.

=7-WEEKDAY(VALUE(RIGHT(B1,4)),12)+7*(LEFT(B1,FIND("/",B1)-1)-2)+DATE(RIGHT(B1,4),1,1)

 

[Jaymond Flurrie]

I have not done comprehensive testing on this but it works on your example. Please check to see if this works for your other data. The "ww/yyyy" value is in B1 for this example.

=7-WEEKDAY(VALUE(RIGHT(B1,4)),12)+7*(LEFT(B1,FIND("/",B1)-1)-2)+DATE(RIGHT(B1,4),1,1)

I think that's a very good start. I noticed that 1/2021 returns December 29th, but the correct result would be something between January 4th and January 10th.

 

[felixstraube]

I think this works:

=LET(
y, B3,
week,C3,
firstDayOfYear, DATE(y,1,1),
firstWeek, SEQUENCE(7,,firstDayOfYear-5),
firstWeeksMonday, MAX(firstWeek*(WEEKDAY(firstWeek,2)=1)),
firstWeeksMonday+7*(week-1)
)

I'm starting with year and week in different columna. I'm missing the parsin, but that is an easy fix.

I think it works ok. For the first week of the year it does not match with WEEKNUM for every year, but that is ok. Because you are returning the monday of the first week of the year.
For example if you have the start of the year 2013. The week of 2013 where WEEKNUM returns 1 for the first time start with sunday 30/12/2012 (week in red text). So the monday of that week is 31/12/2012 which is (I think) the correct answer, because in this week we have the first day of the year, 1/1/2013. Dunno if that makes sense. It did to me.

Try it for other years/weeks and let me know.

 

[KRice]

Here is another option. It may need to be adjusted, depending on the system used (assuming Week 1 is the week containing January 1) and weeks begin on Sunday. Inputs need by formula in blue, output in green. I left some formulas in the sheet to show what certain parts of the formula are doing. EDIT: Based on your comment about 1/2021 ideally returning something between January 4 and January 10, I'm guessing that a different "Week 1" convention is desired (other than Week is the week containing January 1)?

MrExcel_20240112.xlsx
	A	B	C	D
1	Input Date	12/31/2020	12/16/2018	4/18/2013
2	Fwd conversion	53/2020	51/2018	16/2013
3	Parse weeknum	53	51	16
4	Parse year	2020	2018	2013
5	1st Sunday of year	1/5/2020	1/7/2018	1/6/2013
6	1st Monday of year	1/6/2020	1/1/2018	1/7/2013
7	Monday of Weeknum	12/28/2020	12/17/2018	4/15/2013
Sheet3

Cell Formulas
Range	Formula
B2:D2	B2	=WEEKNUM(B1,1)&"/"&YEAR(B1)
B3:D3	B3	=LEFT(B2,FIND("/",B2)-1)+0
B4:D4	B4	=RIGHT(B2,4)+0
B5:D5	B5	=DATE(B4,1,8)-WEEKDAY(DATE(B4,1,7))
B6:D6	B6	=DATE(B4,1,8)-WEEKDAY(DATE(B4,1,6))
B7:D7	B7	=IF(DATE(B4,1,8)-WEEKDAY(DATE(B4,1,6))>DATE(B4,1,8)-WEEKDAY(DATE(B4,1,7)),B3-2,B3-1)*7+DATE(B4,1,8)-WEEKDAY(DATE(B4,1,6))

 

[zero269]

I have like this:
Source Date: December 31st, 2020
Result: 53/2020
Formula: WEEKNUM(A1;21)&"/"&YEAR(A1)

But how about the same backwards, what would you suggest? The exact date would preferably be the particular Monday.

So input: 53/2020 (which can be parsed obviously to 53 and 2020 with a split at the slash)
should somehow calculate 44193 (which is December 28th 2020).

I was thinking of making simply a large lookup table, but I have a feeling that's unnecessary.

Oh and I need to do this without VBA, just by using Excel functions.

Hi Jaymond,

Am I reading you correctly that you wish to ENTER '53/2020' into a cell and then have that entry converted into a 'Monday DATE' for WEEK 53, YEAR 2020?

Just curious, thanks.

 

[felixstraube]

A little shorter version of the formula:

=LET(
y, B3,
week,C3,
firstDayOfYear, DATE(y,1,1),
firstWeeksMonday, WORKDAY.INTL(firstDayOfYear+2,-1,"0111111"),
firstWeeksMonday+7*(week-1)
)

 

[KRice]

@felixstraube, unless the OP hasn't updated their profile, it appears as though Excel 2016 is being used...so the Excel 365 LET function may not, unfortunately, be available. Still, the inner parts of the function would work fine.

 

[zero269]

A little shorter version of the formula:

=LET(
y, B3,
week,C3,
firstDayOfYear, DATE(y,1,1),
firstWeeksMonday, WORKDAY.INTL(firstDayOfYear+2,-1,"0111111"),
firstWeeksMonday+7*(week-1)
)

Hi Felix,

Not to step on your toes, but I realize now that what OP is looking for is to REVERSE his INPUT and OUTPUT process. I though at first when OP stated "backwards", it meant instead of current output of WW/YYYY to be YYYY/WW.

This is way above my paygrade... but I wanted to share a shorter formula I found that produces the same results as yours that I think you may like. I found it on ablebits.

Excel WEEKNUM function – convert week number to date and vice versa

Formula examples demonstrate how you can use the WEEKNUM function to calculate week numbers in Excel - convert week number to date, get week number from date, sum values based on the week number and more.

www.ablebits.com

=DATE(B2, 1, -2) - WEEKDAY(DATE(B2, 1, 3)) + A2 * 7

Best regards,

 

[Jaymond Flurrie]

Hi Jaymond,

Am I reading you correctly that you wish to ENTER '53/2020' into a cell and then have that entry converted into a 'Monday DATE' for WEEK 53, YEAR 2020?

Just curious, thanks.

Input parameter would be 53/2020 string and output would be the date of the Monday of the week 53 of 2020.