Question

Hi

I have an audit planner which i am trying to automate some what, i would like the status to change based on a set criteria.

I.e. AUDIT STATUS = "Awaiting Scheduling" IF IMS AUDIT is CHECKED and SCHEDULED DATE is BLANK

AUDIT STATUS = "Scheduled" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPPETED DATE is BLANK

AUDIT STATUS = "Overdue' IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPETED DATE is BLANK and PROPOSED AUDIT DATE is less than TODAY.

AUDIT STATUS = "Completed" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPLETED DATE is NOT BLANK

Any help would be greatly appreciated

Brett Wyrick · Accepted Answer

This ought to do it: =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 = ""), "Scheduled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))

Parker Oxford · Accepted Answer

See formula below, it should work if you copy and paste into your column so let me know if anything doesn't work. If there are any issues, ensure I got all the column names correct. =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=""),"Scheduled",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row="",[IMS Audit Proposed Audit Month]@row"",[IMS Audit Completed Date]@row<>"", "Completed",""))))