timeseries in wrong format

[pc1404]

Hi,

I am trying to analyze monthly income and volume data; problem is that the data is in following format

Client Name	Income Jan 18	Income Feb 18	Volume Jan 18	Volume Feb 18	Visits Jan 18	Visits Feb 18
Client name 1	2343	43434	4434	42234	43234	23424
Client name 2	2342	242345	25345	525	453	535

<tbody>
</tbody>

When making a pivot table the columns headed Jan 18 / Feb 18 etc are (rightfully) not recognized as dates; would there be an easy way to transform this data so that I could analyze trends based on the dates ? Background information: the excel containts thousands of rows of clients and about 50 columns containing the date datasets (income jan, income feb, income march etc.. ) and is generated in a big corporate without any possibility to change the way the data is delivered to me

thanks !!

 

[mole999]

you might consider changing the header dates to real one, ie first of Feb, and custom formatting as Feb 18 ie "mmm yy"

 

[pc1404]

you might consider changing the header dates to real one, ie first of Feb, and custom formatting as Feb 18 ie "mmm yy"

Thanks for the suggestion. I think two big issues with that:

1) I have multiple columns containing data for the same time period ; so one column labelled income feb 18; another labelled sales feb 18; another labelled turnover feb 18. So renaming them to feb 18 and formatting that cell as date wouldn't work.

2) I'll receive this excel with over 50 columns every month; so would be easier/better if somehow i can make the process of transforming the date automatic (best would be to change the source I know; but that just won't be possible).

 

[Guenther45]

May be that you can solve your problem wit Power Query ...
Because I don't know how to attach a *.xlsx I put it in my own server until end of year 2018: http://www.excel-ist-sexy.de/wp-con...8/PQ_solution_timeseries_in_wrong_format.xlsx (hope, that's ok)