Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 |
#5
|
|||
|
|||
Whoops, I see we just passed along eachother, thanks a lot for your help
anyway! Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
calculate no. of years between a date and today's date | Excel Worksheet Functions | |||
calculate date differences in years and months | Excel Worksheet Functions | |||
Calculat years married or age of someone on today's date | Excel Worksheet Functions | |||
How do I set up a date cell to and 15 years in another cell? | Excel Worksheet Functions |