Year number of week 53

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
Hi Everyone,

I use a formula that shows yearnumber/weeknumber of a date i get in column A
However I noticed that it goes wrong with the year number.
1601460054338.png

See the image above ^:
I need this to be 2020/53 since in column C i am using a VLookup function that searches for 2020/53.
B5 = =CONCATENATE(YEAR(A5);"/";WEEKNUM(A5;21))
I hope I informed you guys enough.

Thanks in advance,
Ram
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
Will not you have a similar issue with some December dates as well? E.g., shouldn't 29-Dec-2025 be converted into "2026/1"?

Try the following formula -- it should work for any date:
Excel Formula:
=YEAR(A1-WEEKDAY(A1,3)+3)&"/"&WEEKNUM(A1,21)
This worked well, I haven't checked all the other answers I have gotten, I didn't think I would get so many answers... Thanks!
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,595
On behalf of all contributors to this thread -- you are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,996
Messages
5,543,188
Members
410,584
Latest member
Bluefox68
Top