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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
682
This should work but may cause issues if you roll to future years

=CONCATENATE(YEAR(A5-1);"/";WEEKNUM(A5;21))
 

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
This should work but may cause issues if you roll to future years

=CONCATENATE(YEAR(A5-1);"/";WEEKNUM(A5;21))
Sorry but this didn't help at all. EDIT: nothing changed
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,297
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=CONCATENATE(YEAR(IF(WEEKNUM(A2,21)>=52,EDATE(A2,-1),A2)),"/",WEEKNUM(A2,21))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,428
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Does this work...
Excel Formula:
=YEAR(A1)-(WEEKNUM(A1,21)>52)&"/"&WEEKNUM(A1,21)
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,600
... I noticed that it goes wrong with the year number...
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)
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,109

ADVERTISEMENT

is that what you want?
DateDateYW
11/12/202011/12/20202020/50
18/12/202018/12/20202020/51
25/12/202025/12/20202020/52
01/01/202101/01/20212021/53
30/12/202130/12/20212021/53

post more representative example with expected result. use XL2BB to do that
 

Watch MrExcel Video

Forum statistics

Threads
1,114,339
Messages
5,547,360
Members
410,789
Latest member
cpolek83
Top