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]

 

[Joe4]

Welcome to the Board!

You want to use a Nested IF, i.e.

=[COLOR=#0000ff]IF(B2="","No deadline set"[/COLOR],[COLOR=#ff0000]IF(B2 < TODAY(),"Overdue",<today(),"overdue",< font="">[COLOR=#008000]"Not yet completed"[/COLOR]</today(),"overdue",<>[/COLOR]<today(),"overdue",< font="">))

See: https://www.techonthenet.com/excel/formulas/if_nested.php
for more details.</today(),"overdue",<>

 

[AliGW]

Try this:

=IF(B2="","NDS",IF(A2="","NYC",IF(A2>B2,"O","")))

Replace the acronyms with your phrases. There are, of course, other scenarios not accounted for here, but you can nest a couple more.

 

[Joe4]

I think I misunderstood how you want the second example to work.
If that is the case, then AliGW's response will probably be the one you want.

 

[Callaghan86]

Thank you very much. That works perfectly.

 

[Callaghan86]

Thank you,

1 more question. What if the deadline expires but no completion date has been entered? I want to say "If TODAY > deadline date AND completion date is blank, then "Overdue"

Thanks,

Chris

 

[Joe4]

I can't help thinking there might be an easier way, but this does seem to work:

=IF(B2="","No deadline set",IF(AND(A2="",B2 > TODAY()),"Not yet completed",IF(AND(B2 < TODAY(),A2=""),"Overdue",IF(AND(B2 < TODAY(),A2 <> "",A2 > B2),"Overdue",""))))

 

[Callaghan86]

Thank you very much for your help. This makes perfect sense. I was unsure how to use the IF(AND) function.

 

[Joe4]

You are welcome.
Glad to help!

 

[Callaghan86]

Thanks to you guys I have now expanded on it to include more information

=IF(C2="","Please set deadline",IF(A2="","No date planned",IF(AND(B2="",TODAY()>C2),"Task is "&TODAY()-C2&" days overdue!",IF(B2="","Not yet completed",IF(AND(C2=TODAY(),B2<>""),"Deadline today! Task already complete",IF(B2<C2,"Task completed on time",IF(AND(C2-TODAY()<30,B2=""),(C2-TODAY())&" days until deadline",IF(AND(B2>C2,B2=""),(C2-B2)&" days overdue",IF(B2>C2,"Task completed "&(B2-C2)&" days late")))))))))