Round date to first of month
 

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.

Dominic

if the date is in cell B1, try:

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

Regards

Trevor


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

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

Many thanks to you and Trevor. Works great.



"Bob Phillips" wrote:

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