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 

It seems like you want to check multiple conditions using the IF function to determine different statuses ('Completed', 'Pending', or 'Not Completed') based on various date columns (L, P, and V).

Your current formula is close, but it might need some adjustments to cater to all the conditions you mentioned. Try this updated formula:

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

This formula checks:

• If the date in column L is in the future compared to today, it returns "Completed".
• If both P4 and V4 are in the future compared to today, it returns "Pending".
• If none of the conditions are met, it returns "Not Completed".

This formula assumes that if P4 or V4 is in the future, it is "Pending". Adjust the logic if both shouldn't be considered as "Pending" simultaneously. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@SGeorgie 

I started to try to create a spreadsheet to write and test a formula for you, but realized I don't fully understand the various possible conditions. For one thing, it's not clear whether one can expect that either:

• all three cells (L4, P4 and V4) will be blank, OR
• any one of the three cells, but only one of them, will have a value, OR
• some combination of two OR three of the cells can have a value

In the first case--where they're all blank--it's clear that "Not completed" is the desired result, but it's not clear (to me at any rate), what we're dealing with beyond that. How many of the cells will have a value: only one, any two, all three? 

Could you please clarify. If possible, describe the setting, what the context is.

AND, ideally, since you're clearly working with an existing spreadsheet, would you be able to post a copy of that actual spreadsheet along with your explanation, putting it on OneDrive or GoogleDrive, with a link pasted here that grants access to it. Not an image, an actual working spreadsheet.

[#4]

Hi, so in my data we have a completed date column
a pending date column
and a pending approval date column

if ALL three are blank i'd like the formula to produce 'Not Completed'

If there is only a Completed date but its expired the formula should show expired

if there is either a pending or pending approval date in the future it should say pending
if either of these dates are in the past it should say expired

[#5]

Thanks nearly there
So scenarios are:
Completed - If there is only a completed date in the future
Not Completed - if L4, P4 and V4 ALL blank
Pending - If P4 or V4 in future
Expired - if L4 or P4/V4 in past

This formula needs to include Expired and if L4 blank but P4 future should say Pending - see example

I would also need to include somehow if L4 is in the past it brings back 'Expired' too?

[#6]

@SGeorgie    

[#7]

screenshot is attached as quite sensitive info

[#8]

@SGeorgie 

Your answer doesn't seem to cover all the possibilities. You have to realize, that you, since you're IN the situation, consider the answer to the following question is obvious and therefore not worth stating.

This is a question I would be asking if we were sitting down face to face, and I apologize if it seems so obvious to you:

Is it ever the case that two or more of the cells L4, P4 and V4 will have values in them?

I understand what happens if any one of them has a value, but to me it's not clear whether two or more might have a value and what happens then if those values are inconsistent.

And since that still needs answering, let me pose another, which gets at the basic design.

In your most recent post you referred to those cells by labels (perhaps a column heading) and those labels themselves are

• Completed
• Pending
• Pending Approval

meaning that whoever is entering the data needs to pick a column that corresponds to the state of pending or completion--in other words., that entry already presumes the answer to the question this IF function is proposing to answer--so WHY do you even need a formula that answers the question??!!

That is a serious question.That's a big part of why I asked before if it is possible for you to post a copy of the spreadsheet itself. Especially since two of the cells apparently are going to contain dates that reflect--or appear to reflect--almost identical data: "Pending" and "Pending Approval"   It just sounds like a confusing design, and part of what @NikolinoDE and I (and others on this site) offer is help in making sure spreadsheet designs make sense, not just that formulas work.

[#9]

@SGeorgie Seeing that image does not allay my questions about the basic design....that aside, I'm wondering if you could just employ the IFS function, rather than nesting IFs several levels deep. The hyperlink here will take you to a resource that describes IFS.

[#10]

@SGeorgie 

=IF(AND(L4>TODAY(),N4>TODAY(),P4>TODAY()),"Completed",
IF(OR(P4>TODAY(),V4>TODAY()),"Pending",
IF(AND(ISBLANK(L4),ISBLANK(N4),ISBLANK(V4)),"Not Completed",
IF(OR(L4<TODAY(),AND(P4<TODAY(),V4<TODAY())),"Expired",""))))

Does this return the intended result?

[#11]

@OliverScheurich    I tried that but It is only L4, P4 and V4 not N so  changed that to the relevant column but where there is not date in either column it is bringing back Expired

[#12]

so yes they may do and this is when you have a past completed date and then EITHER a pending or a Pending approval date

They are purely date columns for if it is a completed certificate, one which is pending or one which is pending approval - we need to clearly see where we are compliant and where we are not.

[#13]

i attach dummy data

[#14]

@mathetes 

Apologies I cannot attach the excel as 

Here are all scenarios

ALL Expiry Date columns BLANK - this should say 'Not Completed'

Only Completed expiry date completed (in future) - this should say 'Compliant'

Only Completed expiry date completed (in past) - this should say 'Expired'

Pending and Pending Pending Approval with future date - this should say 'Compliant' -

This has both Completed and Pending - dates in future - should say 'compliant'

if Completed date were in past but Pending in future it should still say 'Compliant' 

You will not see Complete (past) and pending/pending approval (past)

That is all the scenarios i can see

[#15]

@OliverScheurich   Hi, no its not quite right, its showing Expired when all L, P and V are empty and the completed date (L) is in the future

But correct here: 

[#16]

i used =IF(AND(L2701>TODAY(),P2701>TODAY(),P2701>TODAY()),"Completed",
IF(OR(P2701>TODAY(),V2701>TODAY()),"Pending",
IF(AND(ISBLANK(L2701),ISBLANK(P2701),ISBLANK(V2701)),"Not Completed",
IF(OR(L2701<TODAY(),AND(P2701<TODAY(),V2701<TODAY())),"Expired",""))))

[#17]

i am not sure i can wrk out how to do IFS as all examples are based on 1 cell not multiple

[#18]

forgot to mention L, P and V having formulas in them so the columns should be K, O and U

This works to the biggest extent - but some completed Expired ones are saying compliant not Expired....

There are also 2 rows with Pending Expiry as 2023-01-06 which are coming back as Pending but should say Expired (no other dates for that one )


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

[#19]

@SGeorgie 

i am not sure i can wrk out how to do IFS as all examples are based on 1 cell not multiple

Those are only examples, not the only possible ways to construct the conditions in an IFS formula.

The key point, as contrasted with IF, is that in IFS you have to enter the conditions in an order/sequence in recognition of the fact that:

• The IFS function does not go through each and every condition.
• The IFS function stops evaluating as soon as it meets a condition/consequence in which the condition is satisfied. 

Also important, once you've got it working, IFS is a lot more "readable" to the human eye and brain, than a deeply nested set of IF conditions.

You can easily construct something like

=IFS(AND(condition1,condition2),consequenceA,OR(condition3,condition4),consequenceB,.......)

making it as complicated with those AND / OR combinations as the situation warrants.

[#20]

so my formula which works for the most part is

=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")

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

Please can you help get this formula right Mathetes and/or Oliver