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.

 

[felixstraube]

Hi everyone. One more testing.
After carefully reviewing the formulas presented, I would like to share my findings and provide some evidence to support my perspective.
Using the test I proposed earlier: Starting from a date that is a monday, generating the string week/year for that date, and then applying the each formulas to convert back to the date.
Starting from a century worth of mondays from 1950 to 2050 (52*100 mondays) I've got this results. Conditional formatting is set so that it is painted red if the input date do not match with the formula result. In row 1 is the number of matching dates for each formula.

Being the formulas:
felixstraubes's:

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

Tetra201's:

=WORKDAY.INTL(DATE(RIGHT(C3,4),1,0),1,"1110111")+LEFT(RIGHT(0&C3,7),2)*7-10

6StringJazzer's:

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

Krice's:

=IF(DATE(RIGHT(C3,4)+0,1,8)-WEEKDAY(DATE(RIGHT(C3,4)+0,1,6))>DATE(RIGHT(C3,4)+0,1,8)-WEEKDAY(DATE(RIGHT(C3,4)+0,1,7)),LEFT(C3,FIND("/",C3)-1)+0-2,LEFT(C3,FIND("/",C3)-1)+0-1)*7+DATE(RIGHT(C3,4)+0,1,8)-WEEKDAY(DATE(RIGHT(C3,4)+0,1,6))

Don't know if I did some mistake. Please check the formulas and see if they are correct.

 

[KRice]

@felixstraube, thanks for running these comparisons, but would you mind substituting my revised formula shown in cell B3 of post #16? I made some changes to the formula in my original post to reflect the definitions (for week 1 and week start day) and implemented an input error check.

 

[Tetra201]

@felixstraube: Try using the OP formula from Post #1 for getting the "week/year" values -- it returns, for example, "49/1955" for 5-Dec-1955, not "50/1955" as shown in your comparison.

 

[felixstraube]

@KRice sorry about that. Here it is with the formula in post 16:

formula being:

=LET(wy,C3,w,LEFT(wy,LEN(wy)-5)+0,y,RIGHT(wy,4)+0,weoy,WEEKNUM(DATE(y,12,31),21),maxw,IF(weoy=1,52,weoy),deght,DATE(y,1,8),fstTh,deght-WEEKDAY(DATE(y,1,3)),fstMo,deght-WEEKDAY(DATE(y,1,6)),IF(w>maxw,"max wknum 52",IF(fstMo>fstTh,w-2,w-1)*7+fstMo))

 

[felixstraube]

@felixstraube: Try using the OP formula from Post #1 for getting the "week/year" values -- it returns, for example, "49/1955" for 5-Dec-1955, not "50/1955" as shown in your comparison.

Ohh, thanks. My mistake.
With the formulas as before (KRice's corrected one):

Of course mine isn't working properly I didn't realize the 21 as the second parameter in WEEKNUM.
Your formulas are the best in the comparison with just 3 days with different result.

 

[Tetra201]

Those "3 days with different result" are caused by the imperfection of the OP "week/year" formula -- in its current form, it returns "1/1951" for 31-Dec-1951. The correct value, however, should be "1/1952".

 

[KRice]

@felixstraube, interesting...you've shown some good test cases. I agree with @Tetra201's comment. The initial conversion for end of year Monday's is sometimes being miscalculated as week 1 of that same year rather than week 1 of the following year. I've used this site to quickly check some dates/week numbers.

Year 1952 Calendar – Finland

Finland 1952 – Calendar with holidays. Yearly calendar showing months for the year 1952. Calendars – online and print friendly – for any year and month

www.timeanddate.com

 

[felixstraube]

I agree with your both. I also did some testing and yes, the problem I see is that with the formula WEEKNUM(<date>, 21) there will be some years with two weeks with the number 1, one at the start and one at the end.

I think, for this reverse WEEK/YEAR to Date calculation, the system used in WEEKNUM with parameter 21 should be avoided, as it will be prone to errors.

 

[Tetra201]

As the OP pointed out in Post #13, they use the ISO week numbering system in Finland (Week 1 is the week with the first Thursday of the year), and this can hardly be avoided.

However, the imperfection of the OP "week/year" formula can be easily fixed -- for example, by using:

=WEEKNUM(A1,21)&"/"&YEAR(WORKDAY.INTL(A1-1,1,"1111110"))

Edit: ^^^ This formula has a glitch -- please see Post #31 for a corrected formula.

 

[felixstraube]

You are absolutely right. I didn't think it through. Still got some differences though:

42 in total.

This might correct it:

=WEEKNUM(A3,21)&"/"&YEAR(A3)+(WEEKNUM(A3,21)=1)*(MONTH(A3)=12)

It seem ok now:

100% match!

Great teamwork!