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

should do it

--

HTH

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


"Dominic" wrote in message
...
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.