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.

 

[Jaymond Flurrie]

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.

View attachment 104918​

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

View attachment 104919
Best regards,

The input should be WW/YYYY. With backwards I mean that when original one takes A as the input and gives B as the result, the reversed version takes B as the input and gives A as the result.

 

[Jaymond Flurrie]

@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.

Updated. I'm using 365.

 

[Jaymond Flurrie]

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))

At least here in Finland the January 1 is not necessarily on Week 1. January 1 can be also on Week 53. That happens when the week following week 52 has its Thursday belonging to the old year (i.e. more old year days than new year days). So the week following Week 52 can be either Week 53 (Thursday belongs to the old year) or Week 1 (at least four new year days). Here's more info: ISO week date - Wikipedia

Example of 52 week year: 2023
Example of 53 week year: 2020

I will check those formulas you sent, I can see that at least you have tried, I'm very grateful for that

 

[felixstraube]

You can test my function the other way around...
Starting from every monday you can think of, getting year and week (with WEEKNUM) and feeding those to the formula I provided, and see if you get the same date again. Just for testing purposes of course, i know you problem is different.

Like this (sorry still no desktop app with XL2BB):

 

[felixstraube]

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,

Testing your formula:

Doesn't seem to give the right answer.

 

[KRice]

Thanks for the clarification about week numbering and week start conventions. I was working with a different set of assumptions. Also, thanks for updating your profile to show the latest version of Excel being used...that often makes a difference regarding which functions can be used and how the formulas can be written.

Here is an updated formula that uses input in the form of ww/yyyy, splits the text apart to determine the week number and year. Checks to see what the latest week number possible is for that year and displays an error message if the input ww exceeds the maximum possible for that year. Beyond that, the methodology is similar to what I showed before, except I'm targeting different dates to determine how to adjust the number of weeks from a baseline date. Please check carefully, but this seems to test okay for me. The single formula in the yellow-shaded cells is the only one needed for the result. The other formulas were used to return intermediate results for my reference during testing. If desired, this formula could be converted to a function that accepts a single argument (ww/yyyy).

MrExcel_20240112.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Input ww/yyyy	53/2020	52/2018	16/2013	52/2021	26/2014	7/2015	7/2016	1/2014	53/2014
3	Monday of Weeknum	12/28/2020	12/24/2018	4/15/2013	12/27/2021	6/23/2014	2/9/2015	2/15/2016	12/30/2013	max wknum 52
4
5	Parse weeknum	53	52	16	52	26	7	7	1	53
6	Parse year	2020	2018	2013	2021	2014	2015	2016	2014	2014
7	1st Thursday of year	1/2/2020	1/4/2018	1/3/2013	1/7/2021	1/2/2014	1/1/2015	1/7/2016	1/2/2014	1/2/2014
8	1st Monday of year	1/6/2020	1/1/2018	1/7/2013	1/4/2021	1/6/2014	1/5/2015	1/4/2016	1/6/2014	1/6/2014
Sheet3

Cell Formulas
Range	Formula
B3:J3	B3	=LET(wy,B2,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))
B5:J5	B5	=LEFT(B2,LEN(B2)-5)+0
B6:J6	B6	=RIGHT(B2,4)+0
B7:J7	B7	=DATE(B6,1,8)-WEEKDAY(DATE(B6,1,3))
B8:J8	B8	=DATE(B6,1,8)-WEEKDAY(DATE(B6,1,6))

 

[felixstraube]

Another way to look at it is that if you take the formula I posted in post 7:

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

And feed it first year 2014 and week 53, and then year 2015 week 1, it should return the same result as it is actually the same week.

 

[felixstraube]

And here is the formula with the parsing:

In Cell B2 enter this formula and copy down:

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

 

[felixstraube]

One more analysis. To me it looks correct. What do you think @Jaymond Flurrie ?

 

[Tetra201]

@Jaymond Flurrie: See if the following formula works for you:

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