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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Income Jan 18[/TD]
[TD]Income Feb 18[/TD]
[TD]Volume Jan 18[/TD]
[TD]Volume Feb 18[/TD]
[TD]Visits Jan 18[/TD]
[TD]Visits Feb 18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client name 1[/TD]
[TD]2343[/TD]
[TD]43434[/TD]
[TD]4434[/TD]
[TD]42234[/TD]
[TD]43234[/TD]
[TD]23424[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client name 2[/TD]
[TD]2342[/TD]
[TD]242345[/TD]
[TD]25345[/TD]
[TD]525[/TD]
[TD]453[/TD]
[TD]535[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)