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!

 

[Aladin Akyurek]

=YEAR(A2)&" "&WEEKNUM(A2,2)

should work as intended...

 

[Julia_P]

Now I get for 1/1/2016: 2016 1. Firstly, in the European system, the first week of the year needs to include at least 4 days of January, which means that 1/1/2016 should be considered in the last week of 2015 and not in the first week of 2016. Secondly, I need the week to have two digits (so 01 instead of 1)

 

[Aladin Akyurek]

Now I get for 1/1/2016: 2016 1. Firstly, in the European system, the first week of the year needs to include at least 4 days of January, which means that 1/1/2016 should be considered in the last week of 2015 and not in the first week of 2016. Secondly, I need the week to have two digits (so 01 instead of 1)

If you are on Excel 2013 or beyond...

=YEAR(A2)&" "&TEXT(ISOWEEKNUM(A2),"00")

 

[Julia_P]

It still results year 2016:
12/31/2015 2015 53
1/1/2016 2016 53

These two days are in the same week and I need it to show the same year and week number, respectively 2015 53

 

[Aladin Akyurek]

It still results year 2016:
12/31/2015 2015 53
1/1/2016 2016 53

These two days are in the same week and I need it to show the same year and week number, respectively 2015 53

Here is a tweak:

=YEAR(A2)-(ISOWEEKNUM(A2)<>WEEKNUM(A2,2))&" "&TEXT(ISOWEEKNUM(A2),"00")

Hope it survives further scrutiny...

 

[Julia_P]

It fixes the week 53 2015 but what should be week 1 2016 results in 2015 01:

1/3/2016 2015 53
1/4/2016 2015 01

 

[Aladin Akyurek]

It fixes the week 53 2015 but what should be week 1 2016 results in 2015 01:

1/3/2016 2015 53
1/4/2016 2015 01

Care to post the input dates in the format 7-Jul-2012 and the expected results next to it?

 

[Julia_P]

Here it is:

03-Jan-16 2015 53
04-Jan-16 2016 01

 

[Aladin Akyurek]

Here it is:

03-Jan-16 2015 53
04-Jan-16 2016 01

Does this cover all eventualities?

=YEAR(A2)-(DAY(A2)<4)*(ISOWEEKNUM(A2)<>WEEKNUM(A2,2))&" "&TEXT(ISOWEEKNUM(A2),"00")