A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

First and Last Day of the Quarter



 
 
Thread Tools Display Modes
  #1  
Old March 3rd 06, 11:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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!
Ads
  #2  
Old March 3rd 06, 11:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=518912

  #3  
Old March 3rd 06, 11:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=518912

  #4  
Old March 3rd 06, 11:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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)

"daddylonglegs" >
wrote in message
news:daddylonglegs.244akc_1141428902.6934@excelfor 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)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:

http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=518912
>



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


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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=518912

  #6  
Old March 4th 06, 01:19 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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)
>
> "daddylonglegs" >
> wrote in message
> news:daddylonglegs.244akc_1141428902.6934@excelfor 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)
> >
> >
> > --
> > daddylonglegs
> > ------------------------------------------------------------------------
> > daddylonglegs's Profile:

> http://www.excelforum.com/member.php...o&userid=30486
> > View this thread: http://www.excelforum.com/showthread...hreadid=518912
> >

>
>
>

  #7  
Old March 4th 06, 02:10 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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

(remove ^^ from email address)

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)
>>
>> "daddylonglegs"
>> >
>> wrote in message
>> news:daddylonglegs.244akc_1141428902.6934@excelfor 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)
>> >
>> >
>> > --
>> > daddylonglegs
>> > ------------------------------------------------------------------------
>> > daddylonglegs's Profile:

>> http://www.excelforum.com/member.php...o&userid=30486
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=518912
>> >

>>
>>
>>


 




Thread Tools
Display Modes

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


All times are GMT +1. The time now is 07:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.