Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-17-2022, 05:27 AM
tomohawk tomohawk is offline Issue With Date In Pivot Table Windows 10 Issue With Date In Pivot Table Office 2019
Advanced Beginner
Issue With Date In Pivot Table
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default 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
Attached Images
File Type: jpg Year Issue.jpg (28.7 KB, 19 views)
Reply With Quote
  #2  
Old 01-17-2022, 11:53 AM
p45cal's Avatar
p45cal p45cal is offline Issue With Date In Pivot Table Windows 10 Issue With Date In Pivot Table Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

I think we'd need to see a representative workbook…
Reply With Quote
  #3  
Old 01-17-2022, 12:13 PM
tomohawk tomohawk is offline Issue With Date In Pivot Table Windows 10 Issue With Date In Pivot Table Office 2019
Advanced Beginner
Issue With Date In Pivot Table
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default

Hi p45cal

Please see attached

Tommy
Attached Files
File Type: xlsx Sample_Pivot_Dates_Check.xlsx (30.2 KB, 5 views)
Reply With Quote
  #4  
Old 01-17-2022, 03:12 PM
p45cal's Avatar
p45cal p45cal is offline Issue With Date In Pivot Table Windows 10 Issue With Date In Pivot Table Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Attached Files
File Type: xlsx msofficeforums48329Sample_Pivot_Dates_Check.xlsx (34.3 KB, 5 views)
Reply With Quote
  #5  
Old 01-18-2022, 02:24 AM
tomohawk tomohawk is offline Issue With Date In Pivot Table Windows 10 Issue With Date In Pivot Table Office 2019
Advanced Beginner
Issue With Date In Pivot Table
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 01-18-2022, 08:23 AM
p45cal's Avatar
p45cal p45cal is offline Issue With Date In Pivot Table Windows 10 Issue With Date In Pivot Table Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #7  
Old 01-18-2022, 08:42 AM
tomohawk tomohawk is offline Issue With Date In Pivot Table Windows 10 Issue With Date In Pivot Table Office 2019
Advanced Beginner
Issue With Date In Pivot Table
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default

Thanks p45cal

I realised that my base data had two formats for date, so when that was fixed, I was able to group
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Filter Issue mikeinli Excel 1 09-23-2020 01:26 PM
Issue With Date In Pivot Table 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
Issue With Date In Pivot Table Pivot Table issue ominae Excel 1 07-18-2014 01:40 AM
Pivot Table Issue Grovesy Excel 0 10-28-2012 09:08 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:19 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft