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.
An example of the scenarios in the table is below. The dates are in MM/DD/YYYY format.
<tbody>
</tbody>
<tbody>
</tbody>
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.
- No-Value
- Complete
- On-Time
- Past-Due
An example of the scenarios in the table is below. The dates are in MM/DD/YYYY format.
DUE DATE | COMPLETION DATE | OUTPUT | DESCRIPTION |
No-Value | Self explanatory | ||
04/27/2019 | Complete | No Due Date value so Complete only | |
01/13/2020 | On-Time | On-Time because Due Date is in the future with no completion. | |
03/09/2019 | Past-Due | Past Due because Due Date is in the past with no completion. | |
10/05/2019 | 09/10/2019 | On-Time | On-Time because completed before the Due Date. |
03/30/2019 | 04/22/2019 | Past-Due | Past Due because completed after the Due Date. |
<tbody>
</tbody>
<tbody>
</tbody>
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.