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" <Dominic@discussions.microsoft.com> wrote in message
news:B2E560EE-4776-4D1D-A0AF-D719D7BB0D58@microsoft.com...
> 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" <Dominic@discussions.microsoft.com> wrote in message
news:B2E560EE-4776-4D1D-A0AF-D719D7BB0D58@microsoft.com...
> 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" <Dominic@discussions.microsoft.com> wrote in message
> news:B2E560EE-4776-4D1D-A0AF-D719D7BB0D58@microsoft.com...
> > 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.
>
>
>