Changing Date for Pivot Table Result
 

I have a column of dates, 10,000 of them, that I need to change from the
entered date covering over 4 years, to dates that all show the 1st day of the
month. Example: Date Entered=10/3/2008 to 10/1/2008 or 9/23/2007 to
9/1/2007. I need to do this so I can use the Pivot Table feature to give me
results based on specific periods of time, such as months and then quarters.
How can I accomplish this?

Changing Date for Pivot Table Result
 

Actually, you don't need to do this.

You can use the dates in your pivottable, but then rightclick on the date field
and choose:

Group and Show Detail|Group

Then group by months (and year???) or quarter.

But if you wanted, you could add another column and use a formula like:

=text(a2,"yyyymm")
(to return text)
or
=date(year(a2),month(a2),1)
(to return a date (the first of the month).)



dls61721 wrote:

I have a column of dates, 10,000 of them, that I need to change from the
entered date covering over 4 years, to dates that all show the 1st day of the
month. Example: Date Entered=10/3/2008 to 10/1/2008 or 9/23/2007 to
9/1/2007. I need to do this so I can use the Pivot Table feature to give me
results based on specific periods of time, such as months and then quarters.
How can I accomplish this?

--

Dave Peterson

Changing Date for Pivot Table Result
 

Thanks to both Dave and Roger for responding. I will use both as I was not
familiar with the grouping feature in Pivot Tables.

Doug

"Dave Peterson" wrote:

Actually, you don't need to do this.

You can use the dates in your pivottable, but then rightclick on the date field
and choose:

Group and Show Detail|Group

Then group by months (and year???) or quarter.

But if you wanted, you could add another column and use a formula like:

=text(a2,"yyyymm")
(to return text)
or
=date(year(a2),month(a2),1)
(to return a date (the first of the month).)



dls61721 wrote:

I have a column of dates, 10,000 of them, that I need to change from the
entered date covering over 4 years, to dates that all show the 1st day of the
month. Example: Date Entered=10/3/2008 to 10/1/2008 or 9/23/2007 to
9/1/2007. I need to do this so I can use the Pivot Table feature to give me
results based on specific periods of time, such as months and then quarters.
How can I accomplish this?

--

Dave Peterson