In our Group By Date post, we grouped our sales numbers by weeks.
However there are times when we try to group by Dates and we get an error message! That is frustrating!
Instead of checking the dates one by one to find out where the error occurred, I will show you a cool way to get to those problematic dates & fix them in one go!
We already have the pivot table ready for grouping by Dates:
In the example below I show you how to get the Errors when Grouping By Dates:
STEP 1: Right click on any row in your Pivot Table and select Group so we can select our Group type that we want:
However we notice that we have an error!
STEP 2: To check where our error occurred, go to the data table and highlight the column that contains our dates.
STEP 3: Go to Home > Find & Select > Go To Special:
Make sure the Constants, Text and Errors are selected. This will select all our invalid dates (Errors) and text data (Text).
STEP 4: Excel has now selected the incorrect dates. To make each incorrect cell easier to view go to Home > Fill Color
The invalid dates are now highlighted:
STEP 5: Manually replace the incorrect dates with the correct dates:
STEP 6: We need to Refresh our pivot table to load our new correct dates but first we need to “uncheck” the ORDER DATE field.
Right click on the Pivot Table and click Refresh:
“Check” the ORDER DATE Field:
STEP 7: Right click on the Pivot Table and click Group:
The Grouping Options are now displayed! Your data is now clean!