If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 First and Last Day of the Quarter
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## First and Last Day of the Quarter

#1
March 4th 06, 12:16 AM posted to microsoft.public.excel.worksheet.functions
 Wolfspaw external usenet poster Posts: n/a
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!
#2
March 4th 06, 12:34 AM posted to microsoft.public.excel.worksheet.functions
 daddylonglegs external usenet poster Posts: n/a
First and Last Day of the Quarter

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)

--
------------------------------------------------------------------------

#3
March 4th 06, 12:41 AM posted to microsoft.public.excel.worksheet.functions
 daddylonglegs external usenet poster Posts: n/a
First and Last Day of the Quarter

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)

--
------------------------------------------------------------------------

#4
March 4th 06, 12:47 AM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips external usenet poster Posts: n/a
First and Last Day of the Quarter

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)

wrote in message
news:[email protected] um-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)
>
>
> --
> ------------------------------------------------------------------------

http://www.excelforum.com/member.php...o&userid=30486
>

#5
March 4th 06, 01:40 AM posted to microsoft.public.excel.worksheet.functions
 daddylonglegs external usenet poster Posts: n/a
First and Last Day of the Quarter

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

--
------------------------------------------------------------------------

#6
March 4th 06, 02:19 AM posted to microsoft.public.excel.worksheet.functions
 Wolfspaw external usenet poster Posts: n/a
First and Last Day of the Quarter

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)
>
> wrote in message
> news:[email protected] um-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)
> >
> >
> > --
> > ------------------------------------------------------------------------

> http://www.excelforum.com/member.php...o&userid=30486
> >

>
>
>

#7
March 4th 06, 03:10 AM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom external usenet poster Posts: n/a
First and Last Day of the Quarter

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

Portland, Oregon

"Wolfspaw" > wrote in message
...
> 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)
>>
>> >
>> wrote in message
>> news:[email protected] um-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)
>> >
>> >
>> > --
>> > ------------------------------------------------------------------------

>> http://www.excelforum.com/member.php...o&userid=30486
>> >

>>
>>
>>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

All times are GMT +1. The time now is 03:25 AM.