Week format into Month (for Pivot Table)

[koskesh]

Hi,

I have a huge dataset I want to analyze in a pivot table.
One issue I have:

The weeks are formated like this: 36.2014
(WEEK, Year)

I need a formula to change it to the month.
Or is it easier to change it manually?

Thanks for the help!

 

[Asheesh]

@koskesh

Suppose 36.2014 is in cell A1 then try the below formula in Cell B2

MONTH(DATE(RIGHT(A1,LEN(A1)-SEARCH(".",A1)),1,(LEFT(A1,SEARCH(".",A1)-1))*7-2)-WEEKDAY(DATE((LEFT(A1,SEARCH(".",A1)-1)),1,3)))

If you wish to see the month name then use

CHOOSE(MONTH(DATE(RIGHT(A1,LEN(A1)-SEARCH(".",A1)),1,(LEFT(A1,SEARCH(".",A1)-1))*7-2)-WEEKDAY(DATE((LEFT(A1,SEARCH(".",A1)-1)),1,3))),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Regards
Asheesh

 

[Luke M]

Sounds doable, but clarification needed.
Does your system count Jan 1 as week 1, or week 0? Or, is week 1 the first full week? What day of the week does your week being on (some countries start with Sunday, some with Monday)

 

[koskesh]

Thanks for the quick reply.
This is a bit tricky. SAP BI shows for example Week 44 twice (October,November)

This is how it looks now (I want the Months instead):

36.2014 37.2014 38.2014 39.2014 40.2014 40.2014 41.2014 42.2014 43.2014 44.2014 44.2014 45.2014 46.2014 47.2014 48.2014 49.2014 50.2014 51.2014 52.2014 01.2015 01.2015 02.2015 03.2015 04.2015 05.2015 05.2015 06.2015 07.2015 08.2015 09.2015 09.2015

My system should start at mondays

 

[Luke M]

So, 01.2015 is ~ Jan 5, 2015 = January?
Further checks:
04.2015 ~ Jan 26, 2015 = January
05.2015 = February
09.2015 = March
16.2015 = April
Can you confirm that this is how you want the week numbers translated?

 

[Luke M]

If above assumptions are correct, formula to convert your text into dates (which can be formatted to show only months, and/or better handled in PTs:
=DATE(RIGHT(A2,4),1,CHOOSE(WEEKDAY(DATE(YEAR(A2),1,1),2),5,4,3,2,1,7,6))+(LEFT(A2,LEN(A2)-5)-1)*7

 

[koskesh]

Yes your assumptions are correct.
Luke you are my hero!! works perfect. Thank you!!