# 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
###### Who is Participating?

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.

Excel & 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
Author 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
Author 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