Nested IF/AND/OR? Excel formula to determine results based on comparing 'cut-off date'with 'work order complete' date for each of 3 departments

I have tried unsuccessfully to create a nested IF/AND formula whereby if two conditions are met, then the work order is 'On Time', otherwise it is 'Late'.

Unit_Timeliness:
IF UDS![Unit]="DC", AND (UDS![Date_WO_Completed] > Schedule![DC_Cut_Off], THEN "Late" OR
IF UDS![Unit]="IFP", AND (UDS![Date_WO_Completed] > Schedule![IFP_Cut_Off], THEN "Late" OR
IF UDS![Unit]="PA", AND (UDS![Date_WO_Completed] > Schedule[PA_Cut_Off], THEN "Late"
ELSE "On Time"
If error, then leave cell BLANK

The following formula I came up with is not working properly, in that all the results are indicating that the work orders were completed 'Late', which is not the case:

=IF(AND(OR([@Unit]="DC",[@[Date_WO_Completed]]-Schedule.xlsx!AIM_Schedule[DC_Cut_Off]>"0",[@Unit]="PA",[@[Date_WO_Completed]]-Schedule.xlsx!AIM_Schedule[PA_Cut_Off]>"0",[@Unit]="IFP",([@[Date_WO_Completed]]-Schedule.xlsx!AIM_Schedule[IFP_Cut_Off]>"0"))),"Late","On Time")

Details of attached files:
UDS.xlsx
Column name: [Unit]
Contains one of the following 3 variables, which are acronyms for 3 different departments:
DC
PA
IFP

Column name: [Date_WO_Completed]
Contains date that work order was completed by the department

Schedule.xlsx
Column names:
[DC_Cut_Off]*
[IFP_Cut_Off]*
[PA_Cut_Off]*
*Contains 'Cut-off' date for each department against which the work order's completion date is measured against to determine if they delivered the work order 'On Time' or 'Late'.

Files as attached.

I hope I have provided enough clarity and background...

Thanks,
Andrea
UDS.xlsx
Schedule.xlsx
AndreamaryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Andrea,

I move Schedule.xlsx Sheet1 to UDS.xlsx and applied below formula:
=IFERROR(IF(OR([@Unit]={"DC","IFP","PA"},[@[Date_WO_Completed]]>AIM_Schedule[DC_Cut_Off],[@[Date_WO_Completed]]>AIM_Schedule[IFP_Cut_Off],[@[Date_WO_Completed]]>AIM_Schedule[PA_Cut_Off]),"Late","On Time"),"")
I changed few cells in Unit to "Open" to check if formula works. As you have "DC", IFP" & "PA" in all your cells.
Is this what you want?
Andrea_UDS.xlsx
0
AndreamaryAuthor Commented:
Hi Shums,

Thanks for the quick response! :-)

I reviewed the spreadsheet, and noticed that some of the results were not correct. Perhaps I wasn't clear enough in my original post, and if so, my apologies! For instance:
  • The Cut-off date for DC is 13-Sep-2017 (as listed in the Schedule spreadsheet), so any 'DC' work orders that have a 'Date_WO_Complete' (Column D of UDS spreadsheet) that is later than 13-Sep-2017 should be showing as 'Late', but they are showing as 'On Time'.
  • There are instances where the ''Date_WO_Complete' (Column D of UDS spreadsheet) is blank, in which case I would like the 'Unit_Timeliness' column to remain blank (handling it as an error).
  • The Unit column will always be populated, so I don't need error-handling for blanks in that column.

Let me know if you have any questions or need any clarification...

Thanks!
Andrea
0
AndreamaryAuthor Commented:
Actually, I think I figured this one out! This formula appears to be giving me the results I'm after:

=IFERROR(IF(AND([@Unit]="DC",[@[Date_WO_Completed]]-AIM_Schedule[DC_Cut_Off]>0),"Late",IF(AND([@Unit]="IFP",[@[Date_WO_Completed]]-AIM_Schedule[IFP_Cut_Off]>0),"Late",IF(AND([@Unit]="PA",[@[Date_WO_Completed]]-AIM_Schedule[PA_Cut_Off]>0),"Late","On Time"))),"")

Cheers,
Andrea
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
I continued working on this after I posted the question, and eventually landed a formula that worked...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.