Trying to get the next date exactly 5 years ago..
 

I use the following function(s) in a list of dates (ascending, obviously) to

=VLOOKUP(DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1)

This gives me either today, or the last occurance of the same day 5 years
ago if today is not available. I know what causes this, but can anybody give
me a not too complicated formula for getting the next one?

Thanks a lot!

Mike

Never mind, I've figured it out myself:

=IF(VLOOKUP(DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1)=DATE(YEAR(NOW( ))-5;MONTH(NOW());DAY(NOW()));INDIRECT(ADDRESS(MATCH( DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1);1));INDIRECT(A DDRESS(MATCH(DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1)+1;1)))

Thanks anyway,

Mike

I don't know why it would give you today??
If every day is present It will give you 5 years ago, but if every date is
not present, it would give you the last date before the 5 years ago date.

I would add a ",false" to the end of your lookup formula. this would make
it give only the 5 year ago date or an error if not present.
I am not sure what you mean by "next one" if you mean ten years ago try
=if(iserror(VLOOKUP(DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1,False)),VLOOKUP (DATE(YEAR(NOW())-10;MONTH(NOW());DAY(NOW()));$A:$A;1,False),VLOOKUP (DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1,False))




"Tsjah" wrote:

I use the following function(s) in a list of dates (ascending, obviously) to

=VLOOKUP(DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1)

This gives me either today, or the last occurance of the same day 5 years
ago if today is not available. I know what causes this, but can anybody give
me a not too complicated formula for getting the next one?

Thanks a lot!

Mike

"bj" wrote in message
...
I don't know why it would give you today??
If every day is present It will give you 5 years ago, but if every date is
not present, it would give you the last date before the 5 years ago date.

That's what I was trying to say, forgot a comma..

Mike

"Tsjah" wrote:

I use the following function(s) in a list of dates (ascending, obviously)
to

=VLOOKUP(DATE(YEAR(NOW())-5;MONTH(NOW());DAY(NOW()));$A:$A;1)

This gives me either today, or the last occurance of the same day 5 years
ago if today is not available. I know what causes this, but can anybody
give
me a not too complicated formula for getting the next one?

Thanks a lot!

Mike

Whoops, I see we just passed along eachother, thanks a lot for your help
anyway!

Mike