Logic eludes me

[ben simpson]

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.

[random1970]

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 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 of the VLOOKUP table 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.