Round date to first of month

D

Dominic

Hey all,
I'm trying to round date entries to the first of the month so that I can
PivotTable on the date field.
I've tried using the Month and Year functions in a formula in a helper
column but the result isn't recognized as a date (even if I add a "1" in
between the two).
I can't seem to find the answer in a previous post.
Does anyone have any ideas?
Thanks in advance.

A simple example follows:

A B C
Sacramento 8/4/04 $45
Sacramento 8/9/04 $65
San Francisco 9/1/04 $200
Los Angeles 12/3/04 $60
Los Angeles 12/10/04 $75

I'm trying to get the PivotTable to sum on 8/04 or 8/1/04, 9/04, and 12/04.

Thanks again.
 
T

Trevor Shuttleworth

Dominic

if the date is in cell B1, try:

=DATE(YEAR(B1),MONTH(B1),1) in your helper column

Regards

Trevor
 
B

Bob Phillips

=DATE(YEAR(B2),MONTH(B2),1)

should do it

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dominic

Many thanks to you and Trevor. Works great.



Bob Phillips said:
=DATE(YEAR(B2),MONTH(B2),1)

should do it

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top