First and Last Day of the Quarter

I am looking for two formulas that would return the first and last day of the
quarter based on any given date. Assuming the given date is 2/14/03, I would
like to return the following in separate cells:

1/1/2003
3/31/2003

Thanks!

If your date is in A1 this formula gives the first day of the quarter

=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3),1)

and this the last day

=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,0)

Nice use of FLOOR and CEILING but I think the first formula should be

=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+1,1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"daddylonglegs" <daddylonglegs.244akc_1141428902.6934@excelforum-nospam.com>
wrote in message
news:daddylonglegs.244akc_1141428902.6934@excelforum-nospam.com...
>
> If your date is in A1 this formula gives the first day of the quarter
>
> =DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3),1)
>
> and this the last day
>
> =DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,0)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=518912
>

Thanks Bob. You are, of course, correct, nice catch

Thank you so much! I was not even aware of the Floor and Ceiling functions.

May I ask one more favor? I would like to fill the neighboring cells with
the next quarter. Again using the date 2/14/03 and your formulas already
provided, I am looking for the following:

1/1/2003 4/1/2003 7/1/2003 etc.
3/31/2003 6/30/2003 9/30/2003 etc.

Thanks again.




"Bob Phillips" wrote:

> Nice use of FLOOR and CEILING but I think the first formula should be
>
> =DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+1,1)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "daddylonglegs" <daddylonglegs.244akc_1141428902.6934@excelforum-nospam.com>
> wrote in message
> news:daddylonglegs.244akc_1141428902.6934@excelforum-nospam.com...
> >
> > If your date is in A1 this formula gives the first day of the quarter
> >
> > =DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3),1)
> >
> > and this the last day
> >
> > =DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,0)
> >
> >
> > --
> > daddylonglegs
> > ------------------------------------------------------------------------
> > daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> > View this thread: http://www.excelforum.com/showthread...hreadid=518912
> >
>
>
>

Use the built in logic of the formulas

=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+4,1)

=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+7,1)

and so on for second and third

=DATE(YEAR(A1),CEILING(MONTH(A1),3)+4,0)

=DATE(YEAR(A1),CEILING(MONTH(A1),3)+7,0)

and so on

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Wolfspaw" <Wolfspaw@discussions.microsoft.com> wrote in message
news:B5124FAF-2314-47F0-9676-36ABB4591AEC@microsoft.com...
> Thank you so much! I was not even aware of the Floor and Ceiling
> functions.
>
> May I ask one more favor? I would like to fill the neighboring cells with
> the next quarter. Again using the date 2/14/03 and your formulas already
> provided, I am looking for the following:
>
> 1/1/2003 4/1/2003 7/1/2003 etc.
> 3/31/2003 6/30/2003 9/30/2003 etc.
>
> Thanks again.
>
>
>
>
> "Bob Phillips" wrote:
>
>> Nice use of FLOOR and CEILING but I think the first formula should be
>>
>> =DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+1,1)
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from email address if mailing direct)
>>
>> "daddylonglegs"
>> <daddylonglegs.244akc_1141428902.6934@excelforum-nospam.com>
>> wrote in message
>> news:daddylonglegs.244akc_1141428902.6934@excelforum-nospam.com...
>> >
>> > If your date is in A1 this formula gives the first day of the quarter
>> >
>> > =DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3),1)
>> >
>> > and this the last day
>> >
>> > =DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,0)
>> >
>> >
>> > --
>> > daddylonglegs
>> > ------------------------------------------------------------------------
>> > daddylonglegs's Profile:
>> http://www.excelforum.com/member.php...o&userid=30486
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=518912
>> >
>>
>>
>>