#1
|
|||
|
|||
Issue With Date In Pivot Table
I have a spreadsheet with a large amount of data from which I create a pivot table for analysis.
In the data, one of the columns contains a date and from that date, I use a formula "=YEAR(A830)" and I have it formatted as "Number" However, when I use this field, in a pivot table, I get an incorrect answer (1905). In the sample attached, I should see 2018, 2019, 2020 and 2021. What am I doing wrong? Also, and for my edification, I would like to be able to use MONTH in the pivot table at some point. I currently use formula "=DATE(YEAR(A830),MONTH(A830),1)" and the cell is formatted as "MMM-YY". I suspect this is not the best way to get the desired result, especially for pivot table use. Thanks Tommy |
#2
|
||||
|
||||
I think we'd need to see a representative workbook…
|
#3
|
|||
|
|||
Hi p45cal
Please see attached Tommy |
#4
|
||||
|
||||
You've formatted a number eg.2005 as a date (yyyy) so it's converting the date number (2005) which is 27th June 1905, and only showing the year part (1905).
In the pivot table, go into the field settings for Year, and change the number format back to a plain number or General). 2022-01-17_225824.png But I'm fairly sure you're using a fairly up-to-date version of Excel and you can dispense with your 2 added columns by grouping the date column. I've added another example pivot table which is based only on source data columns A:G with such grouping. |
#5
|
|||
|
|||
Thanks for the support p45cal.
Problem sorted. I'm interested in applying your recommendation regarding grouping. Would you mind sending me a link on how to do that. Tommy |
#6
|
||||
|
||||
How to Group Dates in Pivot Tables in Excel (by Years, Months, Weeks)
Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data - Excel Campus If your version of Excel allows you to add a timeline, you may not even need to group the dates at all; the timeline will do it for you - the dates don't even need to appear in the pivot table. 2022-01-18_151844.png and: 2022-01-18_151701.png |
#7
|
|||
|
|||
Thanks p45cal
I realised that my base data had two formats for date, so when that was fixed, I was able to group |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot Table Filter Issue | mikeinli | Excel | 1 | 09-23-2020 01:26 PM |
Pivot table dates issue | salse | Excel | 1 | 06-08-2016 12:23 PM |
Excel pivot table with a DATE value field for some reason stops at a certain date | angie450 | Excel Programming | 2 | 08-19-2014 08:50 AM |
Pivot Table issue | ominae | Excel | 1 | 07-18-2014 01:40 AM |
Pivot Table Issue | Grovesy | Excel | 0 | 10-28-2012 09:08 PM |