IF AND formula help !!! Please :o)

paul112233

I have a formula which you guys helped with previous which after using for a few months need to tweek as it not quite working. So the formula is set to to change the status box depending on a set of rules and used for an internal audit planner.

The issue; The formula is a little broken in that in order for the 'outstanding' status to appear there must be a date in the 'scheduled date' field, this means that those audits which are yet to be scheduled and have gone overdue remain as 'awaiting scheduling' rather than tripping over into 'overdue' once the 'proposed audit month' has passed.

Also have added a 'audit cancelled' column as a checkbox and would like this to change the 'audit status' column to 'cancelled' when the box is checked, when i have added this i get various errors so would love some assistance with this to please.

The current formula as follows;

=IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling",

IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue",

IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Booked",

IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed",

The bit i tried to add and failed...

IF([IMS Audit Cancelled]@row = true, "Cancelled")))))

Thanks in advance to all you guys.

Christian Graf

=IF([IMS Audit Cancelled]@row = true, "Cancelled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Booked", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))))

Does this help?

Christian Graf

This is due to order of operations. If this is not true, then seek further rule. If your rule is at the bottom, the ones above will supersede it.

When you use the if statement like this you are nesting them within the prior statement. Shown below is a better example of what you are doing. If it is false, it can continue to the next statement. The reason it got stuck was it found a different true rule before it reached the end.

IF( cancelled)

Display "canceled"

IF( scheduled but not completed)

Display "awaiting completion"

IF(x)

Display "message"

Hopefully that makes sense

James Keuning

You need to decide how you want to handle the items that have not been scheduled. Presumably you'd like some notice that they need to be scheduled, and you want that notice much earlier than the Scheduled Date. So when you create the record you probably want a "Set Up By Date" which you can use to alert you if you blow that date. So that's one thing. We can help with that.

As for your IF statement, it looks correct to me. What error are you getting? Is [IMS Audit Cancelled] a checkbox column?

paul112233

Hi James

Thanks for your reply.

Yes, would appreciate the help, so the main issue with the current formula is that when the audit date has passed the formula should move every audit status to the audit status of "overdue", which it does, unless there is no scheduled audit date, in which case it keeps the status as awaiting scheduling. So I need help in fixing this.

Secondly if the audit has been cancelled which is indicated with a check in the checkbox column of "IMS audit cancelled" then the status should change to "cancelled".

Let me know if I need to clarify further.

Kind regards

Paul

Christian Graf

=IF([IMS Audit Cancelled]@row = true, "Cancelled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Booked", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))))

Does this help?

paul112233

Hi Christian,

Thanks for the reply, I had figured it out this afternoon luckily by myself in the end but can you advise, why when I put the cancelled and the overdue rules at the start of the formula does it work but not when it was at the end?

Christian Graf

This is due to order of operations. If this is not true, then seek further rule. If your rule is at the bottom, the ones above will supersede it.

When you use the if statement like this you are nesting them within the prior statement. Shown below is a better example of what you are doing. If it is false, it can continue to the next statement. The reason it got stuck was it found a different true rule before it reached the end.

IF( cancelled)

Display "canceled"

IF( scheduled but not completed)

Display "awaiting completion"

IF(x)

Display "message"

Hopefully that makes sense