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?

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

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