How to Round a Date to a Month

[evoxfan]

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.

 

[Jim Thomlinson]

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...

 

[James Perry]

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 Grou

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 Thomlinso

:

Previous Posts In This Thread:

How to Round a Date to a Month
I have data such as
05/10/07 May-07 72,045.0
06/11/07 June-07 27,000.0
06/11/07 June-07 98,468.2
06/28/07 June-07 28,778.8
06/28/07 June-07 92,722.8
07/10/07 July-07 3.7
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.
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 Grou

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 Thomlinso

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Free Online Courses Available for Eggheadcafe.com Users
http://www.eggheadcafe.com/tutorial...8-fc3cf6855293/free-online-courses-avail.aspx