I'm working with date ranges. What I'm looking for is a way to have a cell
given a value on a specific date, and have it remain there thru the remainder
of the date range (confused yet?). This is what i have so far:

=IF(AND(COUNTIF(Named_Range,G12:AG13)+IF(TODAY()= AG13,0,5)),5,0)

At the end of the quarter, this adds my value of 5 into a cell, and then
removes it beyond the end of the quarter. What I need is to have the cell at
a value of 0 prior to the end of the quarter. (Totally confused now?). I
know I am. Help me please, before what little hair I have left if gone for
good.

Ben,

Assuming I'm not confused by your confusion, here are some options.

Option 1:

Lets say your starting value is in cell A1.

In cell A2, type the function =TODAY()

In cell A3 and A4, type in the start and end dates of the date ranges
you're interested in.

Then use an IF function. Let's say for example you have the start date
of the quarter in A3, and the end date in A4.

The value you want, you want to appear in A5.

In cell A5, type if formula =IF(and(A2=A3,A2<=A4),A1+5,A1)

This will return the value in A1+5 if the current date is between the
specified dates (including the start and end dates), and return A1 if
not. If you don't want one or both dates end dates included, remove
the relevant = signs. e.g. instead of =, just type .

Option 2:

Create a VLOOKUP table with key dates in the series, followed by the
number to add in during that period, ensuring your dates are in
ascending order.

Then, again assuming your start value is in A1, and =TODAY() is in A2,
then in A5, type =A1+VLOOKUP(A2,Vlookup_Range,2,true), where
Vlookup_Range is the array in which your VLOOKUP table is, and column 2
has the numbers to add in. This may require a bit of fiddling with
dates until you can get it to work (reading the function description in
Help will help), but it will do the same job.

Hope this helps.


--
random1970
------------------------------------------------------------------------
random1970's Profile: http://www.excelforum.com/member.php...o&userid=32112
View this thread: http://www.excelforum.com/showthread...hreadid=521321