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

 

[JimM]

This should work but may cause issues if you roll to future years

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

 

[Ramballah]

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]

How about

=CONCATENATE(YEAR(IF(WEEKNUM(A2,21)>=52,EDATE(A2,-1),A2)),"/",WEEKNUM(A2,21))

 

[Rick Rothstein]

Does this work...

=YEAR(A1)-(WEEKNUM(A1,21)>52)&"/"&WEEKNUM(A1,21)

 

[Tetra201]

... 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:

=YEAR(A1-WEEKDAY(A1,3)+3)&"/"&WEEKNUM(A1,21)

 

[sandy666]

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]

Does this work...

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

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

Also, if the date is 12/31/2018 then this formula doesn't work.

Take a look at Post # 6