Week Number and Year from Date

[tandkb]

Hello,

I am needing Week Number and Year from the date. I have been using this formula. =TEXT(WEEKNUM(AH2,21),"00") & TEXT(AH2, "-yyyy") and it works quite well. Our Weeks run Monday Through Sunday. The problem I am having is when I get to that date 12/30/19 the Week is 1 which is correct but the year is 2019 which is not correct. The week ends on 1/5/2020 so it should be Week 1 2020 not Week 1 2019. Anyone have any ideas on this? I know this has been talked about a ton but cant find any exact fix for this senario. Thanks in advance!

 

[Fluff]

One way is

=TEXT(WEEKNUM(AH2,21),"00")&TEXT(AH2, "-yyyy")-(AND(MONTH(AH2)=12,WEEKNUM(AH2,21)=1))

Also please update your account details to show your version of Excel, as this affects which functions you can use.

 

[tandkb]

One way is

=TEXT(WEEKNUM(AH2,21),"00")&TEXT(AH2, "-yyyy")-(AND(MONTH(AH2)=12,WEEKNUM(AH2,21)=1))

Also please update your account details to show your version of Excel, as this affects which functions you can use.

This worked perfectly! Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.