CONDITIONAL FORMATTING OR AN 'IF' STATEMENT?

[Carla]

I have a spreadsheet with the following columns:
Col A – Date completed
Col B – Status
Col c – Scheduled start Date
Col D – Scheduled Finish Date

Information for column c and d is copied weekly into my spreadsheet from
another application. I would data entry the dates of the completed work into
column A. in the column B – status, I want any of the following to appear
based on certain criteria – ON TIME, OUT OF SPEC, OR OVERDUE for the dates in
the Date Completed column.
Now the criteria is as follows: the date in Column A is considered on time
if it is completed with the date range of the Scheduled Start Date and the
Scheduled finish Date + or – 5 days on either end. It would be considered
Out of Spec if it were completed outside of this range. And if there isn’t
any date entered in Column A and today’s date is past the scheduled finish
date, I would like it to show as Overdue.

Can anyone help me with this? I am not very experienced with any kind
programming.

 

[Max]

Assuming data in row2 down, with real, unambiguous dates
entered/copied into cols A, C and D

Place in B2
=IF(AND(A2="",C2="",D2=""),"",IF(AND(A2="",D2<>"",TODAY()>D2),"Overdue",IF(AND(COUNT(A2,C22)=3,A2>=C2-5,A2<=D2+5),"On Time","Out Of Spec")))

 

[Carla]

Thanks Max, it works ok with the exception if the Finish Date is in the
future. It is putting Out of Spec in the status cell where it should just be
blank. How do I fix this?

 

[Max]

Try this revision in B2, copied down:
=IF(AND(A2="",C2="",D2=""),"",IF(AND(A2="",D2<>"",TODAY()<=D2),"",IF(AND(A2="",D2<>"",TODAY()>D2),"Overdue",IF(AND(COUNT(A2,C22)=3,A2>=C2-5,A2<=D2+5),"On Time","Out Of Spec"))))