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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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,600
On behalf of all contributors to this thread -- you are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,337
Messages
5,547,355
Members
410,785
Latest member
phillippaige
Top