+ Reply to Thread
Results 1 to 6 of 6

First and Last Day of the Quarter

  1. #1
    Wolfspaw
    Guest

    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. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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. #3
    Bob Phillips
    Guest

    Re: 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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Thanks Bob. You are, of course, correct, nice catch

  5. #5
    Wolfspaw
    Guest

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

    >
    >
    >


  6. #6
    Peo Sjoblom
    Guest

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

    >>
    >>
    >>



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1