How to Round a Date to a Month

I have data such as:
05/10/07 May-07 72,045.00
06/11/07 June-07 27,000.00
06/11/07 June-07 98,468.25
06/28/07 June-07 28,778.80
06/28/07 June-07 92,722.80
07/10/07 July-07 3.79
The first column is the actual date and the second column is the same date
but just formatted different and the third column is cost.

I want to consolidate all cost by month via a pivot table and I have setup
the pivot table but it shows multiple costs for the same month. If I round
the dates in the middle column to the first of the month that they occur, my
pivot table should work.

How can I accomplish this or is there a better way to accomplish my task?

Any help is appreciated, Thanks.

There is a better way. Pivot tables allow for grouping and one of the options
is by month.

Right click on the dates column and select Group

A dialog will open up giving you a number of different ways to group the
data. So long as all of the data in that column is dates then Month will be
one of the options...
--
HTH...

Jim Thomlinson


"evoxfan" wrote:

I have data such as:
05/10/07 May-07 72,045.00
06/11/07 June-07 27,000.00
06/11/07 June-07 98,468.25
06/28/07 June-07 28,778.80
06/28/07 June-07 92,722.80
07/10/07 July-07 3.79
The first column is the actual date and the second column is the same date
but just formatted different and the third column is cost.

I want to consolidate all cost by month via a pivot table and I have setup
the pivot table but it shows multiple costs for the same month. If I round
the dates in the middle column to the first of the month that they occur, my
pivot table should work.

How can I accomplish this or is there a better way to accomplish my task?

Any help is appreciated, Thanks.

Assuming the date is set in Cell A1, set the formula in Cell B1 to:

=(A1-(DAY(A1))+1)

That was the way I did it. Hope this helps (14 months later )



James_Thomlinso wrote:

There is a better way.
30-Jan-09

There is a better way. Pivot tables allow for grouping and one of the options
is by month.

Right click on the dates column and select Group

A dialog will open up giving you a number of different ways to group the
data. So long as all of the data in that column is dates then Month will be
one of the options...
--
HTH...

Jim Thomlinson


"evoxfan" wrote:

Previous Posts In This Thread:

On Friday, January 30, 2009 2:43 PM
evoxfa wrote:

How to Round a Date to a Month
I have data such as:
05/10/07 May-07 72,045.00
06/11/07 June-07 27,000.00
06/11/07 June-07 98,468.25
06/28/07 June-07 28,778.80
06/28/07 June-07 92,722.80
07/10/07 July-07 3.79
The first column is the actual date and the second column is the same date
but just formatted different and the third column is cost.

I want to consolidate all cost by month via a pivot table and I have setup
the pivot table but it shows multiple costs for the same month. If I round
the dates in the middle column to the first of the month that they occur, my
pivot table should work.

How can I accomplish this or is there a better way to accomplish my task?

Any help is appreciated, Thanks.

On Friday, January 30, 2009 3:07 PM
James_Thomlinso wrote:

There is a better way.
There is a better way. Pivot tables allow for grouping and one of the options
is by month.

Right click on the dates column and select Group

A dialog will open up giving you a number of different ways to group the
data. So long as all of the data in that column is dates then Month will be
one of the options...
--
HTH...

Jim Thomlinson


"evoxfan" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Free Online Courses Available for Eggheadcafe.com Users
http://www.eggheadcafe.com/tutorials...ses-avail.aspx