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" wrote in message
...
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" wrote in message
...
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,"")


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

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?


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