10 years after certain period, or not

[EHS]

Hi,

I have the following question: I'm looking for some way to determine whether a certain date falls at least 10 years (and/or 15 years, 25 year, 50 years etc.) after a certain period.

To make it more clear, I have the date of an event (f.e. 24-04-1995), and I have a period during which someone was alive (f.e. 03-02-1920 - 15-06-1859). Now I want to know whether that event has taken place more than x years after the person died.

I have already used the following formula to determine whether the date fell within the living period:

=IF(AND(D2>=DATEVALUE(LEFT(C2;FIND(" - ";C2)));D2<=DATEVALUE(RIGHT(C2;LEN(C2)-FIND(" - ";C2)-2)));1;"0")

But now I want to know whether it falls a certain number of years behind the period. Can anyone help me?

Thanks!

Regards,
Edward

[SteveG]

Edward,

Try,

=DATEDIF(DATEVALUE(LEFT(A2,10)),C2,"y")<10

This assumes the person's date of death is always the 10 leftmost characters in the text string in A2, C2 is the date you are testing. Change the <10 as needed.

HTH

Steve

[oldchippy]

Hi,

I have the following question: I'm looking for some way to determine whether a certain date falls at least 10 years (and/or 15 years, 25 year, 50 years etc.) after a certain period.

To make it more clear, I have the date of an event (f.e. 24-04-1995), and I have a period during which someone was alive (f.e. 03-02-1920 - 15-06-1859). Now I want to know whether that event has taken place more than x years after the person died.

I have already used the following formula to determine whether the date fell within the living period:

=IF(AND(D2>=DATEVALUE(LEFT(C2;FIND(" - ";C2)));D2<=DATEVALUE(RIGHT(C2;LEN(C2)-FIND(" - ";C2)-2)));1;"0")

But now I want to know whether it falls a certain number of years behind the period. Can anyone help me?

Thanks!

Regards,
Edward

Hi Edward,

Have a look at this link for dates before 1900

http://j-walk.com/ss/excel/usertips/tip028.htm