Deb from Wisconsin noticed that her date grouping was wiped out after refreshing a pivot table. Episode 1201 takes a look at how to find the bad dates and solve the problem. Learn Excel 97-2007 from MrExcel!
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
Oh, I love this question sent in by Deb from Wisconsin.
Deb has a Pivot table, and you can see that she's taking the daily dates and roll them up to years and months, but, next month she gets new data.
So, let's copy this new data, Ctrl+C, and paste it down below the data that was used in the Pivot table.
Now since this is already a table, all I have to do is paste here, Ctrl+V, and go back to my Pivot table and refresh, BAM- Oh, but check that out, my group dates went back to daily dates, what's up with that?
And when I try and regroup it again, not allowed.
Well, as soon as I read this in Deb’s email, I knew exactly what had happened.
I said “Deb, somewhere in that new data there was an invalid date.
Now, the tough part is figure out where that invalid date is.” Here's what I want to do, I want to select the whole column, and then use Go To Special, so Ctrl+G and then click Special, and I want to find just the constants that are text, alright, click OK.
Well, now the selected cells are the cells I want, I'm actually going to come here and change those to yellow, alright?
And then what can we do to bring those to the top?
We can do Data, Sort, sort by Date, sort on Cell Color, and I want yellow on the top, click OK.
So now all the selected cells will go flying, there they are 2/31/2013, February 31st, invalid date, so they got treated as text.
Alright, now if you had a small data set, I have another great trick here.
You hold down Ctrl and press the ` (the grove accent), and that shows you all the dates as the number of days, but the text ones show up as text, alright.
So let's fix those, let's just assume it was 2/28/2013, there we go.
Alright, now we come back to our Pivot table, couple things we have to do.
First thing: refresh to get that data back into the Pivot table, and then remove the Date field, and put the Date field back in.
Now, we should find that data is now available for grouping, there you are, Group Field, and roll up to months and years, click OK, alright!
So if that has ever happened to you, or if you had data that was grouped, you had no more data into the Pivot table, and now, all of a sudden, they won't let you group the daily dates.
In fact, that the group dates go back to regular dates, well, that's probably because you had invalid date.
Want to thank Dan for sending that question in, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
Oh, I love this question sent in by Deb from Wisconsin.
Deb has a Pivot table, and you can see that she's taking the daily dates and roll them up to years and months, but, next month she gets new data.
So, let's copy this new data, Ctrl+C, and paste it down below the data that was used in the Pivot table.
Now since this is already a table, all I have to do is paste here, Ctrl+V, and go back to my Pivot table and refresh, BAM- Oh, but check that out, my group dates went back to daily dates, what's up with that?
And when I try and regroup it again, not allowed.
Well, as soon as I read this in Deb’s email, I knew exactly what had happened.
I said “Deb, somewhere in that new data there was an invalid date.
Now, the tough part is figure out where that invalid date is.” Here's what I want to do, I want to select the whole column, and then use Go To Special, so Ctrl+G and then click Special, and I want to find just the constants that are text, alright, click OK.
Well, now the selected cells are the cells I want, I'm actually going to come here and change those to yellow, alright?
And then what can we do to bring those to the top?
We can do Data, Sort, sort by Date, sort on Cell Color, and I want yellow on the top, click OK.
So now all the selected cells will go flying, there they are 2/31/2013, February 31st, invalid date, so they got treated as text.
Alright, now if you had a small data set, I have another great trick here.
You hold down Ctrl and press the ` (the grove accent), and that shows you all the dates as the number of days, but the text ones show up as text, alright.
So let's fix those, let's just assume it was 2/28/2013, there we go.
Alright, now we come back to our Pivot table, couple things we have to do.
First thing: refresh to get that data back into the Pivot table, and then remove the Date field, and put the Date field back in.
Now, we should find that data is now available for grouping, there you are, Group Field, and roll up to months and years, click OK, alright!
So if that has ever happened to you, or if you had data that was grouped, you had no more data into the Pivot table, and now, all of a sudden, they won't let you group the daily dates.
In fact, that the group dates go back to regular dates, well, that's probably because you had invalid date.
Want to thank Dan for sending that question in, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
