+ Reply to Thread
Results 1 to 8 of 8

End of the 4 quarters

  1. #1
    ben simpson
    Guest

    End of the 4 quarters

    Is there a formula that can match the end of the 4 quarters (March31,June
    30,September30, and December 31) to todays date so I can initialize a report
    that is due? I'd like to have the number 5 placed in a cell to remind me.
    Thank you for your help.

  2. #2
    Bob Phillips
    Guest

    Re: End of the 4 quarters

    =DATE(YEAR(TODAY()),(INT((MONTH(TODAY())-1)/3)+1)*3+1,0)

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "ben simpson" <bensimpson@discussions.microsoft.com> wrote in message
    news:E4E68170-46C2-408A-B147-79860D92E2F4@microsoft.com...
    > Is there a formula that can match the end of the 4 quarters (March31,June
    > 30,September30, and December 31) to todays date so I can initialize a

    report
    > that is due? I'd like to have the number 5 placed in a cell to remind me.
    > Thank you for your help.




  3. #3
    ben simpson
    Guest

    Re: End of the 4 quarters

    Thanks Bob for looking in. The formula gave me the date of the end of the
    current quarter, but how can I get this to put the value of 5 in the cell
    when the actual date matches this date (3/31)? Or is there a work around by
    placing the formula in a cell then referencing the result to do this?

    "Bob Phillips" wrote:

    > =DATE(YEAR(TODAY()),(INT((MONTH(TODAY())-1)/3)+1)*3+1,0)
    >
    > --
    >
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from the email address if mailing direct)
    >
    > "ben simpson" <bensimpson@discussions.microsoft.com> wrote in message
    > news:E4E68170-46C2-408A-B147-79860D92E2F4@microsoft.com...
    > > Is there a formula that can match the end of the 4 quarters (March31,June
    > > 30,September30, and December 31) to todays date so I can initialize a

    > report
    > > that is due? I'd like to have the number 5 placed in a cell to remind me.
    > > Thank you for your help.

    >
    >
    >


  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Do you mean you want a formula to return 5 on the last day of a quarter only? If so try

    =IF(DAY(NOW()+1)+MOD(MONTH(NOW()),3)=1,5,"")
    Last edited by daddylonglegs; 03-11-2006 at 08:13 PM.

  5. #5
    ben simpson
    Guest

    Re: End of the 4 quarters

    Thanks daddy. Now what I'm missing is for a way that the 5 will remain there
    through the end of the work period, not just there for that 1 day. Its a
    period of less than 1 month, but the dates vary.

    "daddylonglegs" wrote:

    >
    > Do you mean you want a formula to return 5 on the last day of a quarter
    > only? If so try
    >
    > =IF(DAY(NOW()+1)+MOD(MONTH(NOW()),3)=1,5,"")
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=521401
    >
    >


  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    So you want 5 to remain until a specific date and then what, return to zero? then become 5 again on the last day of the next quarter?

    Can you define the end of the next work period?

  7. #7
    ben simpson
    Guest

    Re: End of the 4 quarters

    That is exactly what I,m looking for daddy. The work periods are a rotating
    schedule that lasts 27 days. They overlap the end of the quarters, as well
    as years. Example: work period 1 this calendar year began on 1/22/2006 and
    ran thru 2/17/2006, and so on. This December, work perion 13 begins on
    12/12/2006, and ends on 1/7/2007. I think that whomever it was that thought
    this schedule up was trying to stick it to those that followed---he he he.
    Thanks again.

    "daddylonglegs" wrote:

    >
    > So you want 5 to remain until a specific date and then what, return to
    > zero? then become 5 again on the last day of the next quarter?
    >
    > Can you define the end of the next work period?
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=521401
    >
    >


  8. #8
    ben simpson
    Guest

    Re: End of the 4 quarters

    I forgot...I have the work period hidden in a range on the sheet that can be
    easily referenced, also have the TODAY() hidden for as reference (to avoid
    those pesky volatile warnings). My brain has died, and I just haven't
    realized it yet!!! Thanks.

    "ben simpson" wrote:

    > That is exactly what I,m looking for daddy. The work periods are a rotating
    > schedule that lasts 27 days. They overlap the end of the quarters, as well
    > as years. Example: work period 1 this calendar year began on 1/22/2006 and
    > ran thru 2/17/2006, and so on. This December, work perion 13 begins on
    > 12/12/2006, and ends on 1/7/2007. I think that whomever it was that thought
    > this schedule up was trying to stick it to those that followed---he he he.
    > Thanks again.
    >
    > "daddylonglegs" wrote:
    >
    > >
    > > So you want 5 to remain until a specific date and then what, return to
    > > zero? then become 5 again on the last day of the next quarter?
    > >
    > > Can you define the end of the next work period?
    > >
    > >
    > > --
    > > daddylonglegs
    > > ------------------------------------------------------------------------
    > > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > > View this thread: http://www.excelforum.com/showthread...hreadid=521401
    > >
    > >


+ 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