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?
 
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
 
ShumsDistinguished Expert - 2017Commented:
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:
I continued working on this after I posted the question, and eventually landed a formula that worked...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.