Year number of week 53

[Ramballah]

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.

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

 

[Phuoc]

Take a look at Post # 6

Yes, the formula in post # 6 works well.

 

[Ramballah]

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:

=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]

On behalf of all contributors to this thread -- you are welcome.