How to Round a Date to a Month

  • Thread starter Thread starter evoxfan
  • Start date Start date
E

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