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]

If you don't have the ISOWEEKNUMBER function, then you can use
=WEEKNUM(A2,21)

 

[Rick Rothstein]

I had a misplaced opening parentheses... I think this should work correctly. If you test it, you might want to do so over a 10 year period so that a couple of Leap Years are spanned. Obviously, you will use a formula to mark any differences rather than trying to "eye it".

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

 

[gaz_chops]

100% the same through 3386

 

[Rick Rothstein]

@gaz_chops... Thanks! Supposedly, the code I transcribed it from says it is good up to and including the year 9999. With that said, Fluff's WEEKNUM suggestion in Message #11 seems just a wee bit simpler to use. Just for context, the VBA code that I used to construct my formula was created because in VBA, the DatePart function has a bug in it for week numbers... the VBA code I translated does not.

@Fluff... I was not aware of the 21 argument for WEEKNUM. What I am now trying to figure out is why are there two codes for "System 1" Mondays (2 and 11)?

 

[gaz_chops]

Seems Excel fails with dates beyond 9999, but your formula appears to keep working, can't check the accuracy as nothing else works!!

 

[Fluff]

No idea, what the difference is between 2 & 11 (or 1 & 17)
But originally only 1 & 2 were available, the rest arrived with 2010

 

[Rick Rothstein]

@gaz_chops... Okay, let's say it is correct then. I mean, there will probably be a mad rush to use it for dates after the year 9999 now.

@Fluff... I have XL2010 and never noticed they expanded the argument list.

 

[Fluff]

I've very rarely ever used the weekday function & when I did that was on Excel 2003.
I only discovered the 21 argument because of this thread & in the process the fact that they added the extra options in 2010.