pivot dates not grouping

[weeksy]

Hi Guys

I hope you can help with an excel 2016 issue.

I have a report from which I have created a pivot table. From time to time I need to update the dataset and this is causing a few issues. I am logging errors on files being delivered. The key data fields are:

Filename
Original Date Delivered (formatted as date in format DD/MM/YYYY)
Issue

The pivot has Issue in rows, Count of Issues in Values and Original Date Delivered in Columns.

The existing pivot automatically separated the date value into months, quarters, years from which I selected months so I saw the number of issues by type by month. Once I updated the dataset the Original Date Delivered values were only shown in Days and didn't automatically split out to Months, Quarters and Years. I cannot get this to work no matter what I do.

Several sites I found on google say you can group the values, but this isn't working. I don't have any grouping options from which to choose from, the menu option doesn't exist.

Do you have any advice/suggestions?

Thanks!

 

[Ron Coderre]

Typically, the inability to group "dates" indicates that one or both of the following situations exist:
• The values are not actually dates
• Non-dates and/or text are mixed in the dates.
Does your data have either of those conditions?

 

[weeksy]

Hi Ron

Thanks for the reply. That does seem to be the problem. Although I have selected the whole column and formatted the cells as date (DD/MM/YY) there are 3 cells where any date I enter convert to 30/04/2018 despite having the same formatting applied. I converted all the cells to text and can see that most of the date cells converted to values like 43124 but these 3 cells stayed as 30/04/2018. I recreated the data set and confirmed all values were stored as dates and now the pivot works fine.

Kind regards

Hilary

 

[Ron Coderre]

Just formatting the cells as dates doesn't complete the task. That will just impact any new values.
After formatting....Select the single-column range of "dates" and
• Data.Text-to-Columns....Check: Delimited...Next...Next...Check: Dates....Click: Finish
Does that help?

 

[weeksy]

That's good advice. I'll remember to do that going forward.

Thanks again. Mr Excel forum saves the day once again!