Expunged dates
Posted by Glyn Smith on May 02, 2001 5:38 AM
I am creating a spreadsheet awarding points to individuals. I want these points to be removed after a defined lapse of time, say 5 years. If the aniversary has not been reached, I want an if function to perform another task.
Posted by Mark W. on May 02, 2001 8:05 PM
Glyn, consider creating a data set like this one
in cells A1:E3...
{"Name","Award","Date","Available","Points"
;"Jerry",10,"1/1/1993",FALSE,0
;"Mary",100,"5/5/1995",TRUE,100}
The formula for 'Available' entered into
cell D2 and then copied down is...
=TODAY()<EDATE(C2,100)
The formula for 'Points' entered into
cell E2 and then copied down is...
=B2*D2
Posted by Mark W. on May 02, 2001 8:21 PM
Darn it! My formulas were mangled...
...they should be...
The formula for 'Available' entered into
cell D2 and then copied down is...
=TODAY()< EDATE(C2,100)
The formula for 'Points' entered into
cell E2 and then copied down is...
=B2*D2
Posted by Mark W. on May 02, 2001 9:19 PM
I should mention...
That the 2nd argument of EDATE() is the number of
months that you want to add to the 1st argument.
I sort of goofed by using 100 which is equivalent
to 8 1/3 years. You indicated that you wanted the
cutoff to be 5 years.... so instead use:
=TODAY()< EDATE(C2,60)
...and to make the date set more interesting
use:
{"Name","Award","Date","Available","Points"
;"Jerry",10,"1/1/1993",FALSE,0
;"Mary",100,"5/1/1997",TRUE,100}
