# Year number of week 53

#### Ramballah

##### Active Member
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.

Ram

### 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
This should work but may cause issues if you roll to future years

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

#### Ramballah

##### Active Member
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
Excel Formula:
``=CONCATENATE(YEAR(IF(WEEKNUM(A2,21)>=52,EDATE(A2,-1),A2)),"/",WEEKNUM(A2,21))``

#### Rick Rothstein

##### MrExcel MVP

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

#### Tetra201

##### MrExcel MVP
... 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

is that what you want?
 Date Date YW 11/12/2020 11/12/2020 2020/50 18/12/2020 18/12/2020 2020/51 25/12/2020 25/12/2020 2020/52 01/01/2021 01/01/2021 2021/53 30/12/2021 30/12/2021 2021/53

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

#### Phuoc

##### Board Regular
Does this work...
Excel Formula:
``=YEAR(A1)-(WEEKNUM(A1,21)>52)&"/"&WEEKNUM(A1,21)``
If the date is 12/28/2020 then the result is: 2019/53

One way: =YEAR(A1-(WEEKNUM(A1,21)>=52)*7)&"/"&WEEKNUM(A1,21)

#### Phuoc

##### Board Regular
=YEAR(A1-(WEEKNUM(A1,21)>=52)*7)&"/"&WEEKNUM(A1,21)
Also, if the date is 12/31/2018 then this formula doesn't work.

#### Tetra201

##### MrExcel MVP
Also, if the date is 12/31/2018 then this formula doesn't work.
Take a look at Post # 6

Replies
3
Views
106
Replies
6
Views
132
Replies
5
Views
32
Replies
3
Views
57
Replies
2
Views
44