Formula to Return Training Status with Multiple Outputs

[aeorfi]

I a table with 6 different input scenarios and need to return 4 different outputs depending on the input. The table data is all dates and I would like to fill the Status column with one of the following outputs.

1. No-Value
2. Complete
3. On-Time
4. Past-Due

An example of the scenarios in the table is below. The dates are in MM/DD/YYYY format.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DUE DATE
[/TD]
[TD]COMPLETION DATE
[/TD]
[TD]OUTPUT
[/TD]
[TD]DESCRIPTION
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]No-Value[/TD]
[TD]Self explanatory[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]04/27/2019[/TD]
[TD]Complete[/TD]
[TD]No Due Date value so Complete only[/TD]
[/TR]
[TR]
[TD]01/13/2020[/TD]
[TD][/TD]
[TD]On-Time[/TD]
[TD]On-Time because Due Date is in the future with no completion.[/TD]
[/TR]
[TR]
[TD]03/09/2019[/TD]
[TD][/TD]
[TD]Past-Due[/TD]
[TD]Past Due because Due Date is in the past with no completion.[/TD]
[/TR]
[TR]
[TD]10/05/2019[/TD]
[TD]09/10/2019[/TD]
[TD]On-Time[/TD]
[TD]On-Time because completed before the Due Date.[/TD]
[/TR]
[TR]
[TD]03/30/2019[/TD]
[TD]04/22/2019[/TD]
[TD]Past-Due[/TD]
[TD]Past Due because completed after the Due Date.[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 356"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]


I have tried different combinations of nested formulas and have not been able to produce all of the outputs correctly/consistently.

I am not familiar enough with VBA to create something but if that is the solution that is returned I understand it well enough to make use of it.

I appreciate any help that can be offered.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IF(B2="",IF(A2="","No Value",IF(A2>=TODAY(),"On Time","Past-Due")),IF(A2="","Complete",IF(A2>=B2,"On Time","Past due")))