Using multiple IF functions?

[Callaghan86]

Hi all,

I've created a spreadsheet for work to show job completion dates. I have 3 columns with the following headers:

Job Completion date
Job Deadline date
Overdue?

I want a formula that can show whether a job is still on time or overdue, but also returning text saying "Not yet completed" if a completion date has not been entered, or "No deadline set" if the deadline box is blank. If both date boxes contain a date, then I want the "overdue?" cell to say "On time" or "Overdue"

For example:

Job Completion Date	Job Deadline Date	Overdue?
01/02/2017		No deadline set
	21/02/2017	Not yet completed
10/02/2017	01/02/2017	Overdue

<tbody>
</tbody>

 

[Ezguy4u]

This formula takes into account a job actually finishing on time

=IF(AND(A2="",B2=""),"No deadline set",IF(AND(A2<>"",B2<1),"Job Completed",IF(AND(A2<B2,A2<>""),"Job Completed before deadline",IF(OR(B2<TODAY(),A2>B2),"Overdue",IF(B2>A2,"Not Yet Completed","Job Completed on Time")))))