Comparing Dates across cells through many columns

[burgsrus]

This is what I have so far, and it is a total mess. Mainly because I really don't want to use either TODAY or NOW but I can't come up with something else. I'm mostly a formula newb - I can do basic stuff and figure out what I have done wrong. This is what I have so far:

=IFS(OR(AND(ISDATE(O100),(EDATE(NOW(),-12<=S100), (EDATE(NOW(),-12<=P100)), (EDATE(NOW(),-12<=Q100)), (EDATE(NOW(),-12<=R100))))), "True", "False")

 

[ExceLoki]

what is the second test for your ifs? and with ifs you dont need a true and false, just a true for each test

what is the second part for your or?

 

[Alex Blakenburg]

If the intent is to see if O100 is a date and all of S, P, Q, R are all greater than or equal to today minus 12 mths then something like the below will get you close.

=LET(keyDate,EDATE(TODAY(),-12),
            IF(AND(ISNUMBER(O100),
                             keyDate<=S100,
                             keyDate<=P100,
                             keyDate<=Q100,
                             keyDate<=R100), "True", "False"))

I would take TODAY out of the formula and put it in a cell and refer to that cell. It will make it easier to test and override the date when you need to.
There is no IsDate in the Excel Functions, IsNumber will get you part way there. The next step would be to give a date range that you would consider could be used to validate the date.

 

[burgsrus]

If the intent is to see if O100 is a date and all of S, P, Q, R are all greater than or equal to today minus 12 mths then something like the below will get you close.

=LET(keyDate,EDATE(TODAY(),-12),
            IF(AND(ISNUMBER(O100),
                             keyDate<=S100,
                             keyDate<=P100,
                             keyDate<=Q100,
                             keyDate<=R100), "True", "False"))

I would take TODAY out of the formula and put it in a cell and refer to that cell. It will make it easier to test and override the date when you need to.
There is no IsDate in the Excel Functions, IsNumber will get you part way there. The next step would be to give a date range that you would consider could be used to validate the date.

So, Alex I think I did not explain my thought process correctly. So, what I'm trying to do is check to see if there are any dates in column O. Then column P, Q, R are not mandatory, but if someone has input something in the last 12 months into those, I want to know about it. Column S is mandatory within the last 12 months. I will then need to drag the formula down to a few hundred other fields. That is why I was fooling with the OR/AND mess. I also have figured out I can use ISNUMBER for the O field.

 

[burgsrus]

So, Alex I think I did not explain my thought process correctly. So, what I'm trying to do is check to see if there are any dates in column O. Then column P, Q, R are not mandatory, but if someone has input something in the last 12 months into those, I want to know about it. Column S is mandatory within the last 12 months. I will then need to drag the formula down to a few hundred other fields. That is why I was fooling with the OR/AND mess. I also have figured out I can use ISNUMBER for the O field.

Alex - thanks for pointing me in the right direction. I was able to modify your code and get it working!!

 

[Alex Blakenburg]

Glad to hear it helped. If you want to post your final formula you can mark it as the solution and it may help others.

 

[burgsrus]

Sure thing. This is how I changed your code to get what I wanted:

=Let(KeyDate, EDATE(TODAY(),-12), IF(OR(ISNUMBER(O100), KeyDate<=P100, KeyDate<=Q100, KeyDate<=R100, (AND(KeyDate<=S100))), "True", "False"))