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.
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.
=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.
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.
>
>
>
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.
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
>
>
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?
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
>
>
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
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks