Dates as Column headings no date field

[ToffeeMark]

Hi,

Had a real disaster trying to analyse some data, I think I'm missing something basic I never learned.

The data Looked like it had been lifted from a pivot table.

The title first row had company, territory, product and then Jan-17- Jul 18 as columns.

When I tried to build a pivot, as there wasn't a date field, the pivot fields available were all the individual months and I couldn't group by years, quarters etc.

Never had that issue before, Quiet good on Excel and Access normally, pivots, vlookups etc but this sent me into a tailspin. Is there some simple technique for rectifying this that I missed and need to learn, I'd really appreciate some guidance. Thanks, Mark

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Territory[/TD]
[TD]Product[/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[TD]Jul-17[/TD]
[TD]Aug-17[/TD]
[TD]Sep-17[/TD]
[TD]Oct-17[/TD]
[TD]Nov-17[/TD]
[TD]Dec-17[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]A[/TD]
[TD]10,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]11,00[/TD]
[TD]8,800[/TD]
[TD]1,200[/TD]
[TD]11,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]B[/TD]
[TD]2,000[/TD]
[TD]8,800[/TD]
[TD]10,000[/TD]
[TD]9,800[/TD]
[TD]9,800[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]9,800[/TD]
[TD]10,000[/TD]
[TD]9,800[/TD]
[TD]10,000[/TD]
[TD]8,800[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]C[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]9,800[/TD]
[TD]8,800[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]9,800[/TD]
[TD]8,800[/TD]
[TD]10,000[/TD]
[TD]10,000[/TD]
[TD]8,800[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]A[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]8,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]8,800[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]B[/TD]
[TD]9,800[/TD]
[TD]9,800[/TD]
[TD]8,800[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]C[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]9,800[/TD]
[TD]8,800[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]A[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]B[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]C[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]8,800[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]12,000[/TD]
[TD]9,800[/TD]
[TD]8,800[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trevor G]

When you look at one of the date cells do you just see Jan-17 for instance in the formula bar? If you look at the format via format cells what format is there? A date field does require a proper date even though the format can be changed to mask just month and year.

 

[ToffeeMark]

The date is shown in the formula bar. The problem I had is if I try to build a Pivot Table, there isn't a Date field, there's just all the individual months as fields

 

[Trevor G]

What happens if you create a helper columns taking the a copy of the date fields and paste as values and try to see if that helps when you build the Pivot. When you toggle the sheet to display all formula etc does the dates appear as the serial numbers?

Also if on the Pivot where you have the months have you looked to use the right mouse button and see how the months are grouped?

 

[ToffeeMark]

It's the layout I think that caused me the problem. I'm part way through a fix I think where people have been describing a reverse pivot or changing it from a crosstab format? does that make sense to you?

Anyway essentially the stage I'm at now is using a power query to unpivot a dummy table I made following this link https://www.excel-university.com/unpivot-excel-data/

It seemed to work although the dates were just Jan-17 and not recognised as dates which is maybe where you're coming from? that seems like that could be fixed and then I'll know how to do it in future.

I'm good with excel, but I learn what I need to do a job, I've never had to do that before so I crashed and burned at an assessment centre for a new job

Live and learn though.

 

[sandy666]

after unpivot select left top icon to change format to date

 

[ToffeeMark]

Perfect, yes I should have taken more care before I closed and loaded!, thanks

 

[sandy666]

You are welcome

Have a nice day