IF FORMULA - Help Needed please

[SGeorgie]

Hi

Im trying to write an IF formula and its erroring 

Basically I this formula worked but i needed the 'not completed' part added!

=IF(L4>TODAY(),"Compliant", IF(P4>TODAY(),"Pending",IF(V4>TODAY(),"Pending","Not Completed")))

want to say if 'L4 is today or in the future bring back 'Completed' but if less than today 'Expired'

if P4 or VA are today or in the future to bring back 'Pending', or if none of the columns have any dates 'Not Completed'

HOW DO I WRITE THIS PLEASE?????  URGENT HELP NEEDED

[#2]

@SGeorgie 

so my formula which works for the most part is

but it not showing Expired for any row
and if there is a completed expiry date in the past AND a pending in the future it says Compliant but it should say Pending

I would say, based on those concluding lines, that it's NOT working for the most part.

I truly don't have time to go through in depth and try to reconstruct. What I'd recommend is that you take each condition separately and make sure it is working before you string them together.

That is to say, look at each line in the formula as it's arrayed below. Work on each one separately, each in its own column. Combine them after each one is working.

I can tell you just looking at that first line under IFS, the one I've underlined, where you've put an AND condition inside an OR, that the OR is not working. Have you learned to count the pairs of parentheses? Count 1 for each open paren, subtract 1 for each closing paren. The count goes back to zero before any alternative (OR) condition is described..and then there's the "Not Completed" consequence.

=IFS(

OR(AND(ISBLANK(K4),ISBLANK(O4),ISBLANK(U4))),"Not Completed",

OR(K4>=TODAY()),"Compliant",

OR(AND(K4<TODAY(),OR(O4<TODAY(),ISBLANK(O4)),OR(U4<TODAY(),ISBLANK(U4)))),"Expired",

OR(AND(K4<TODAY(),O4>=TODAY())),"Pending",

OR(AND(K4<TODAY(),U4>=TODAY())),"Pending"

)

[#3]

that bit works ok but thats for yor time, i still am none the wiser, and each part has brackets accordingly.

may need to do it another way

[#4]

@SGeorgie While reading through this thread, I noticed some conflicting statements made about the logic. However, looking at your original question with the first formula your tried, then comparing that to the last formula you tried, I think this might be what you want:

=IFS(AND(ISBLANK(K4),ISBLANK(O4),ISBLANK(U4)), "Not Completed", K4>=TODAY(), "Compliant", OR(O4>=TODAY(), U4>=TODAY()), "Pending", TRUE, "Expired")

The IFS function evaluates each logical test in order and returns the corresponding value for the first TRUE result found. When a logical test returns TRUE, none of the subsequent tests will even be evaluated, so it's important to get the order correct. The above formula is evaluated as follows:

1. The first logical test checks if ALL date cells are blank. If TRUE, it returns "Not Completed" and does not evaluate any further.
2. The second logical test checks if K4 is greater than or equal to TODAY(). If TRUE, it returns "Compliant", regardless of the other dates.
3. For the third logical test, there is no need to check if K4 is less than TODAY(), because that's already a given, since the second logical test returned FALSE. At this point it's only necessary to check if either O4 or U4 is greater than or equal to TODAY(). If TRUE, it returns "Pending".
4. Finally, if all three logical tests return FALSE, that means one or more dates are already entered, and they are all in the past, so there is no reason to conduct any further logical tests. Simply use TRUE for the expression to return "Expired".

I hope this works as expected, or at least serves as a guide for how to use the IFS function correctly to figure out the logic you need.

[#5]

it is still not bringing back any Expired

I have 03/06/2021 in the completed column only and its bringing back Complaint

[#6]

Also one row has completed date (K4) 12/08/2021 but also U4 as 10/10/2024 so should say 'Pending' not 'Compliant'

[#7]

@SGeorgie I just input those dates into my spreadsheet and it returned "Pending" correctly. My only guess would be that your date cells are formatted as Text. Please make sure they are formatted as Dates and try again.

[#8]

@SGeorgie Also, verify that your current systems settings accurately reflect today's date. Other than that, I'm out of ideas. Even the last really long IFS formula you posted works correctly in my spreadsheet, so there must be something going on with either the way your data is formatted, or with your current system date settings.

[#9]

@SGeorgie One last suggestion... make sure your Calculation Options are set to Automatic, or press F9 on your keyboard to recalculate the entire workbook. The most likely reason you're getting incorrect results is that your Calculation Options are set to Manual.

[#10]

I cannot get it to work - no expired shows

ive tried F9
I have made sure all date columns ae Date and correct format - K/O/U is 2019-06-01

Column L, P and V though have =K+1-1 so they display as 01/06/2019


Does that make a difference? Any way I can send you my dummy file?

[#11]

@SGeorgie Please see the attached example file that's confirmed to be working on my system. Download it, if possible, then add or change dates in each column to create various scenarios and see if it's working for you. If not, then the problem most likely lies on your end with either a system setting or Excel setting. If it works correctly, though, and you're still not able to identify the reason why your file is not working, try to attach a dummy file in your next reply (go to the full editor, then find the area to "browse files to attach" below the editor).

[#12]

how can these to do..?

@SGeorgie 

[#13]

how is @SGeorgie