Help with Calculating date range over 7 years from current date

**nr6281** · 08-08-2019, 03:05 AM

Hi,

I am trying to calculate if a date is over 7 years from =today/current date based on criteria.

IF Column F is blank + Column C matches the States in Column A+ Column D date is over 7 years from the current date and Column E has the words in a sentence eg"Release Date", "Marked Date" than the formula should return as TRUE.

The formula should return TRUE only if all the criteria match.

I tried putting something together based on another help I received but seems I am stuck with calculating the date part of it.

attached is a template.

**AliGW** · 08-08-2019, 03:08 AM

I don't understand what the states are supposed to be matching to - is there meant to be a lookup list somewhere?

What was the formula that you were working with before?

**nr6281** · 08-08-2019, 04:05 AM

Hi,

The states are in Column A cell A19 to A27, however I am looking to avoid vlookup as much as possible so if you see the formula in Cell L2

**AliGW** · 08-08-2019, 04:15 AM

Just out of interest, why do you think you need to avoid VLOOKUP?

Your DATEDIF formula is wrong. Try this:

=DATEDIF(TODAY(),D2,"y")

**AliGW** · 08-08-2019, 04:21 AM

A bit of a guess, but see if this encompasses the tests you are trying to do:

=AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,{"CA";"KS";"MD"},0))),AND(COUNT(SEARCH({"Release Date","Marked Date"},E2))),IFERROR(DATEDIF(D2,TODAY(),"y")>7,DATEDIF(TODAY(),D2,"y")<=7))

**nr6281** · 08-08-2019, 04:30 AM

HI thank you this works perfect

**AliGW** · 08-08-2019, 04:31 AM

Great.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**nr6281** · 08-08-2019, 04:43 AM

Sorry One problem I noticed if the date is below 7 years it still shows true,

Should I remove bit of the formula?

DATEDIF(TODAY(),AE414,"y")<=7))

**AliGW** · 08-08-2019, 04:47 AM

I'm not clear on the date calculation you are trying to make.

Please explain in WORDS what you are trying to do with the date. Some dates are before today and some after - tell us what the outcome should be for each date in column D (manually calculated as TRUE or FALSE.

**nr6281** · 08-08-2019, 04:47 AM

Please refer cell marked in green

**AliGW** · 08-08-2019, 04:49 AM

I don't understand. In your mock-up, this should return TRUE. My formula returns TRUE, so what is the problem here?

**AliGW** · 08-08-2019, 04:51 AM

You are going to need to answer my questions in post #9, because the outcomes you asked for in your sample file do not now match what you are saying.

**nr6281** · 08-08-2019, 04:52 AM

NO the formula is quite accurate now if you see All the criteria matching should return as true if one is also not a match it should return as False, So if you see the attached file above in post #10 the cell D10 I changed the date to below <7 years and it still remains true.

SO all the criteria should be a match. 😊

**AliGW** · 08-08-2019, 05:05 AM

You haven't answered my question about the date calculation. I don't fully understand what you are doing with it.

**nr6281** · 08-08-2019, 05:12 AM

AliGw- All the criteria needs to be a perfect match to ensure to value is TRUE if even one is not a Match than the Value should remain as FALSE.

Updated with details in the attachment.

States: In Column A
Words to Match in Column B

The previous Formula given in Column I
Arguments in Column H

**AliGW** · 08-08-2019, 05:22 AM

OK - I know you know what it all means, and you think it should be obvious to me, but from your descriptions so far it is not.

Please explain in WORDS in your next post:

1. Under what circumstances should column C be TRUE?
2. Under what circumstances should column D be TRUE?
3. Under what circumstances should column E be TRUE?
4. Under what circumstances should column F be TRUE?

If you give me a full answer, I'll try to work it out. If you don't answer my questions, I'll step aside, because I feel as if I'm going round in circles. So far you have not addressed my questions at all.

**joris moerings** · 08-08-2019, 03:52 PM

Hi Ali,

The OP got an answer from me on an earlier question. It looks like he/she is moving on from that answer.
I explained to him how to combine the result of the different checks by using the AND function as he/she requires TRUE or FALSE as a result.
i'll try to finish this.

@Nr6281
I understand why Ali doesn't follow the logic your using within your mockup because a few lines should be shown as true while the checks requested do not support the manual answer.

This is the best i can think off with the written checks in the file and the explaination in the posts, disregarding your answers in the mockup.
=AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,$A$19:$A$27,0))),SUMPRODUCT(--(NOT(ISERR(SEARCH($B$19:$B$20,E2)))))>0,DATEDIF(MIN(TODAY(),D2),MAX(D2,TODAY()),"Y")>7)

**AliGW** · 08-08-2019, 05:12 PM

Thanks for stepping in. No wonder I was confused!!!

**nr6281** · 08-09-2019, 12:29 AM

HI,

Thank you for looking into this, the code works fine however one small issue I have is for the dates that is below 7 years from todays date still shows as true that should return as false.

For example 06/10/2001 should return as False still comes as true.

this is the formula I have tweaked to since I dint wanted to lookup using a table.

AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,{"CA";"KS";"MD"},0))),SUMPRODUCT(--(NOT(ISERR(SEARCH({"Release Date","Return Date","Marked Date"},E2)))))>0,DATEDIF(MIN(TODAY(),D2),MAX(D2,TODAY()),"Y")>7)

**nr6281** · 08-09-2019, 01:13 AM

Ali,

1. Column C should turn True only if the states match i:e eg CA, KS, MD, MA, MT, NH
2. D should turn True only if the date in the column is over 7 years from todays date or current date
3. E should turn True only if has the key words such as "Release date" Marked Date or Return date is present in a sentence
4. F should turn true only if a cell is left blank

5. the formula should return as true only if all the above match , even if one of the above is not a match it should return as false.

I apologies if I am not clear as I am not great in framing my question or ask :-)

**joris moerings** · 08-09-2019, 12:15 PM

Can you please explain this:

Originally Posted by nr6281

the dates that is below 7 years from todays date

For example 06/10/2001 should return as False still comes as true.

7 years from today date means any date in the range august 9th 2012 till august 9th 2026. Anything within this range is below 7 years. Any date which is not in this date range is over 7 years, so how is 2001 below 7 years from todays date?

**nr6281** · 08-12-2019, 02:51 AM

HI,

For example Today = 08/12/2019
>7 years from today = 08/13/2026
The above should return TRUE

Today= 08/12/2019
<7 years = 08/12/2005
The above should return FALSE

So any date over 7 years should return as true and anything below that should return False, SO only date range over 7 years should return as TRUE.

With the previous one a date range with 2001 also return TRUE

**AliGW** · 08-12-2019, 02:57 AM

So do you mean up to 7 years either side of today should return FALSE?

**AliGW** · 08-12-2019, 03:05 AM

Try this as the date check:

=AND(TODAY()>=MIN(EDATE(D2,-84),EDATE(D2,84)),TODAY()<=MAX(EDATE(D2,-84),EDATE(D2,84)))

So something like this in all:

=AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,{"CA";"KS";"MD"},0))),SUMPRODUCT(--(NOT(ISERR(SEARCH({"Release Date","Return Date","Marked Date"},E2)))))>0,AND(TODAY()>=MIN(EDATE(D2,-84),EDATE(D2,84)),TODAY()<=MAX(EDATE(D2,-84),EDATE(D2,84))))

EDIT: No, that doesn't work. I need to have another think.

**AliGW** · 08-12-2019, 03:11 AM

I think this should work (it's my last throw of the die, so I hope it does):

=AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,{"CA";"KS";"MD"},0))),SUMPRODUCT(--(NOT(ISERR(SEARCH({"Release Date","Return Date","Marked Date"},E2)))))>0,OR(D2>EDATE(TODAY(),84)-1,D2 < EDATE(TODAY(),-84)+1))

**nr6281** · 08-12-2019, 03:29 AM

Originally Posted by AliGW

So do you mean up to 7 years either side of today should return FALSE?

Ali, only date over 7 years from current date should return as TRUE, So future date that is 7 years and not the past date

If you see the attached file in Row 10 highlighted in Blue The formula returns as True but ideally should be False

**AliGW** · 08-12-2019, 03:31 AM

Have you tried the suggestion in post #25?

This makes absolutely no sense to me (I doubt it will make sense to anybody, to be honest,as it isn't even syntactically correct):

Even though column F, states match and Date is below years the Word Marked Date is present hence returns to false

Should yesterday's date (11 August 2019)return TRUE or FALSE?

**AliGW** · 08-12-2019, 03:38 AM

Is this what you want?

=AND(ISBLANK(F2),NOT(ISNA(MATCH(C2,{"CA";"KS";"MD"},0))),SUMPRODUCT(--(NOT(ISERR(SEARCH({"Release Date","Return Date","Marked Date"},E2)))))>0,D2>EDATE(TODAY(),84)+1)

**nr6281** · 08-12-2019, 04:17 AM

Yes Yesterdays date should come as false and any future dated over 7 years should return true and yes I have tried the post 25

**AliGW** · 08-12-2019, 04:19 AM

And have you tried post #28?

**nr6281** · 08-12-2019, 04:20 AM

This is perfect exactly what I was looking for,

Thank you, sorry to be a trouble but this is it....

**AliGW** · 08-12-2019, 04:26 AM

Glad we got there. If you read back through your thread and look at the explanations you gave in the various files along with incorrect sample results along the way, I think you should be able to see why we were not getting your point.

You need to be much more precise in your requirements in future - this thread should have taken no more than five posts to resolve.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

**AliGW** · 08-12-2019, 04:27 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**jason.b75** · 08-12-2019, 05:35 AM

The number or functions used could be cut down a bit, NOT(ISNA(..)) and NOT(ISERR(..)) are the same as INSUMBER(..) when used with MATCH or SEARCH, although in this case MATCH is not needed either.

Shortening Ali's formula,

Formula:

Please Login or Register  to view this content.

Or a different approach

Formula:

Please Login or Register  to view this content.

borrowed the EDATE bit from Ali's formula.

**AliGW** · 08-12-2019, 05:36 AM

It's funny - there was another thread the other day where DATEDIF was being used and not returning accurate results, so I used EDATE there. That's why I came back to try it here, as it's more precise.

**jason.b75** · 08-12-2019, 05:53 AM

I've started using variations of YEARFRAC as an alternative to DATEDIF, but that is not without fault either. I tried to solve this thread with it but it returns an absolute difference, so would need additional logical tests to determine past or future difference. (Row 10 in the sample file was returning TRUE in error).

**AliGW** · 08-12-2019, 06:01 AM

Yes, that's the issue I was having with DATEDIF.

For anyone reading this thread who does not 'get' the EDATE formula, it could be done this way:

EDATE(TODAY(),(12*7))

In other words, 84 months is the same as 7 years.

**nr6281** · 08-13-2019, 01:31 AM

A question here,

Both the formulas give me accurate results as expected with EDATE in it.

SO what is the difference between EDATE and DATEDIF?

**AliGW** · 08-13-2019, 01:37 AM

Hopefully this will illustrate why EDATE gives a more precise calculation:

Excel 2016 (Windows) 32 bit

	B	C
8	13/08/2019	17/09/2020
9
10	1	13/08/2020

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	B	C
9
10	=DATEDIF(B8,C8,"Y")	=EDATE(B8,12)

Sheet: Sheet1

DATEDIF is only looking at the YEAR part of the dates in the range.

Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Re: Help with Calculating date range over 7 years from current date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Selecting data from a date range based on the current date

[SOLVED] Highlight dates that are 3 years old as of the current date.

[SOLVED] FORMULA FOR CALCULATING YEARS, DAYS, & MONTHS BETWEEN SEVERAL DATE

Bookmarks

Bookmarks

Posting Permissions