Change Date to show Year & Week of the Year

[bobgrand]

Hello all, I hope we are all being safe these days.

I am trying to create a formula to change the date 2/14/2020 to be a Year & Week of the year format.

Examples
DATE ------- Converted to YYWK
2/14/2020 = 2007
2/21/2020 = 2008
2/28/2020 = 2009
3/6/2020 = 2010
3/13/2020 = 2011

Any help would be greatly appreciated.

 

[Fluff]

How about
=(TEXT(A2,"yy")&TEXT(ISOWEEKNUM(A2),"00"))+0

 

[bobgrand]

Thank you so much. That was perfect.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

For those of you, like me, who do not have the ISOWEEKNUM function available to them, I believe (if I have encoded it correctly) that this formula will produce the same results as Fluff's formula... of course I cannot test that, so if someone with that function available will runs some tests, that would be appreciated.

=INT(MOD(MOD(28*MOD((INT(A2+692501)/7),20871)+4383,146096),1461)/28)+1

 

[gaz_chops]

For those of you, like me, who do not have the ISOWEEKNUM function available to them, I believe (if I have encoded it correctly) that this formula will produce the same results as Fluff's formula... of course I cannot test that, so if someone with that function available will runs some tests, that would be appreciated.

=INT(MOD(MOD(28*MOD((INT(A2+692501)/7),20871)+4383,146096),1461)/28)+1

Doesn't appear to be the same

 

[Fluff]

Hello Rick
That gives a weeknumber of 2 for 5th Jan 2020, whereas the ISO weeknum is 1

 

[gaz_chops]

@Rick Rothstein if it helps, weeknum system 1 gives same as yours.

 

[Rick Rothstein]

Hmm, back to the drawing board.

@gaz_chops... I was just about to ask you for the actual week numbers... thanks.

 

[Fluff]

There are also some "Funnies" like next year

+Fluff New.xlsm
	A	B	C
1		ISO	Rick
2	01/01/2019	1	1
3	02/01/2019	1	1
4	03/01/2019	1	1
5	04/01/2019	1	1
6	05/01/2019	1	2
7	06/01/2019	1	2
8	07/01/2019	2	2
9	01/01/2020	1	1
10	02/01/2020	1	1
11	03/01/2020	1	1
12	04/01/2020	1	1
13	05/01/2020	1	2
14	06/01/2020	2	2
15	07/01/2020	2	2
16	01/01/2021	53	1
17	02/01/2021	53	1
18	03/01/2021	53	1
19	04/01/2021	1	1
20	05/01/2021	1	2
21	06/01/2021	1	2
22	07/01/2021	1	2
23	08/01/2021	1	2
24	09/01/2021	1	2
25	10/01/2021	1	2
26	11/01/2021	2	2
Result

Cell Formulas
Range	Formula
B2:B26	B2	=ISOWEEKNUM(A2)
C2:C26	C2	=INT(MOD(MOD(28*MOD((INT(A2+692501)/7),20871)+4383,146096),1461)/28)+1