extracting week number and year from date

[Julia_P]

Hi all,

I need help with extracting the week number and year from a certain date in excel and I don't get the correct information when a week contains different years. I am using this formula:

=CONCATENATE(YEAR(A2)," ",TEXT(WEEKNUM(A2,21),"00"))

and I get these results:
12/31/2015: 2015 53


1/1/2016: 2016 53

The result for 1/1/2016 should be 2015 53 as week 1 2016 starts on 1/4/2016 as per the European system.

Can somebody help me how to get the correct result?

Thanks!

 

[Julia_P]

It's fine for 2016 but not for 2017:

1/3/2017 2016 01
1/4/2017 2017 01

It should actually be 2017 01 for both dates.

 

[Aladin Akyurek]

It's fine for 2016 but not for 2017:

1/3/2017 2016 01
1/4/2017 2017 01

It should actually be 2017 01 for both dates.

Would you add more input and desired output?

 

[Julia_P]

Here it is:

Day Correct week
12/31/2016 2016 52
1/1/2017 2016 52
1/2/2017 2017 01
1/3/2017 2017 01
1/4/2017 2017 01

With the suggested formula (=YEAR(A371)-(DAY(A371)<4)*(ISOWEEKNUM(A371)<>WEEKNUM(A371,2))&" "&TEXT(ISOWEEKNUM(A371),"00")) I get this result:

Day Week
12/31/2016 2016 52
1/1/2017 2016 52
1/2/2017 2016 01
1/3/2017 2016 01
1/4/2017 2017 01

 

[Aladin Akyurek]

Here it is:

Day Correct week
12/31/2016 2016 52
1/1/2017 2016 52
1/2/2017 2017 01
1/3/2017 2017 01
1/4/2017 2017 01

With the suggested formula (=YEAR(A371)-(DAY(A371)<4)*(ISOWEEKNUM(A371)<>WEEKNUM(A371,2))&" "&TEXT(ISOWEEKNUM(A371),"00")) I get this result:

Day Week
12/31/2016 2016 52
1/1/2017 2016 52
1/2/2017 2016 01
1/3/2017 2016 01
1/4/2017 2017 01

Does this far better?

=YEAR(A371)-(ISOWEEKNUM(A371)=53)&" "&TEXT(ISOWEEKNUM(A371),"00")

See also: http://www.mrexcel.com/forum/excel-...-number-using-weeknum-date-x.html#post2301215, which you could adapt.