software

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)

quot;ben simpsonquot; gt; wrote in message
...
gt; Is there a formula that can match the end of the 4 quarters (March31,June
gt; 30,September30, and December 31) to todays date so I can initialize a
report
gt; that is due? I'd like to have the number 5 placed in a cell to remind me.
gt; 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?

quot;Bob Phillipsquot; wrote:

gt; =DATE(YEAR(TODAY()),(INT((MONTH(TODAY())-1)/3) 1)*3 1,0)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;ben simpsonquot; gt; wrote in message
gt; ...
gt; gt; Is there a formula that can match the end of the 4 quarters (March31,June
gt; gt; 30,September30, and December 31) to todays date so I can initialize a
gt; report
gt; gt; that is due? I'd like to have the number 5 placed in a cell to remind me.
gt; gt; Thank you for your help.
gt;
gt;
gt;


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,quot;quot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=521401Thanks 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.

quot;daddylonglegsquot; wrote:

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


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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=521401That 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.

quot;daddylonglegsquot; wrote:

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

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.

quot;ben simpsonquot; wrote:

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