Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-18-2022, 07:33 AM
tomohawk tomohawk is offline Cannot Group Date In Excel Pivot Table Windows 10 Cannot Group Date In Excel Pivot Table Office 2019
Advanced Beginner
Cannot Group Date In Excel Pivot Table
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default Cannot Group Date In Excel Pivot Table


In the attached pivot table, I select the date field and right-click and select Group (I want to display either year or alternatively month.

I get a message "Cannot Group That Selection"

In the File/Options/Data settings, the "Disable automatic grouping of Date/Time" option is unticked.

Tommy
Attached Files
File Type: xlsx Cannot_Group_Pivot_Field.xlsx (46.9 KB, 5 views)
Reply With Quote
  #2  
Old 01-18-2022, 08:45 AM
tomohawk tomohawk is offline Cannot Group Date In Excel Pivot Table Windows 10 Cannot Group Date In Excel Pivot Table Office 2019
Advanced Beginner
Cannot Group Date In Excel Pivot Table
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default

My bad!

Base data had 2 different formats for dates 🤦*♂️
Reply With Quote
  #3  
Old 01-18-2022, 08:50 AM
p45cal's Avatar
p45cal p45cal is offline Cannot Group Date In Excel Pivot Table Windows 10 Cannot Group Date In Excel 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

It's because the the dates in the source data rows 269 and below are not real dates but strings which look like dates. In the attached I've converted them to real dates (using Text-to-Columns) and grouped the dates in the pivot table by years and months.
Attached Files
File Type: xlsx msoffice48338Cannot_Group_Pivot_Field.xlsx (45.8 KB, 4 views)
Reply With Quote
  #4  
Old 01-18-2022, 08:59 AM
tomohawk tomohawk is offline Cannot Group Date In Excel Pivot Table Windows 10 Cannot Group Date In Excel Pivot Table Office 2019
Advanced Beginner
Cannot Group Date In Excel Pivot Table
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default

Thanks p45cal
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot Group Date In Excel Pivot Table Issue With Date In Pivot Table tomohawk Excel 6 01-18-2022 08:42 AM
pivot table group by HFLO Excel 0 06-08-2017 08:29 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
Cannot Group Date In Excel Pivot Table Group Date by Quarter in PIVOT TABLE shilabrow Excel 6 04-24-2014 10:44 AM
Group Table Rows/Column (like Excel Group) eoinymc Word 1 03-11-2014 04:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:18 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