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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Job Completion Date
[/TD]
[TD]Job Deadline Date
[/TD]
[TD]Overdue?
[/TD]
[/TR]
[TR]
[TD]01/02/2017
[/TD]
[TD][/TD]
[TD]No deadline set
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21/02/2017
[/TD]
[TD]Not yet completed
[/TD]
[/TR]
[TR]
[TD]10/02/2017
[/TD]
[TD]01/02/2017
[/TD]
[TD]Overdue
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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")))))