Formula comparing 2 dates

MJ_1

I'm new to Smartsheet and having some issues with a formula comparing 2 dates with or without blanks. I have a start date and a completed date column, where I need to compare the 2 dates to determine the following:

If completed date is greater than the start date the OKR column shows Delayed

If completed date is less than or equal to the start date the OKR column shows On Time

If completed date is blank the OKR column shows blank

If start date is blank the OKR column shows SD missing

I have the following formula to capture the 2 top scenarios which is working for Delayed and On Time, but am a bit lost on how to capture the formula to deal with the blanks.

=IF([ONBOARDING COMPLETION DATE]@row <= [COURSE START DATE (TERM SHEET)]@row, "On Time", IF([ONBOARDING COMPLETION DATE]@row > [COURSE START DATE (TERM SHEET)]@row, "Delayed"))

Any help would be appreciated!

MJ

Nick Korna

Hi @MJ_1 ,

If I'm understanding you correctly this formula should do what you're after:

=IF(ISBLANK([ONBOARDING COMPLETION DATE]@row), "", IF(ISBLANK([COURSE START DATE (TERM SHEET)]@row), "SD Missing", IF([ONBOARDING COMPLETION DATE]@row <= [COURSE START DATE (TERM SHEET)]@row, "On Time", IF([ONBOARDING COMPLETION DATE]@row > [COURSE START DATE (TERM SHEET)]@row, "Delayed"))))

Example data:

For the fill colour I would use conditional formatting to get the desired result. I'm not aware of any way to do this via cell/column formula.

Hope this helps - if you've any questions etc. then just ask! 😊

Nick Korna

@MJ_1,

This should do what you're after:

=IF([Onboarding Status]@row = "Not requested", "N/A", IF([Onboarding Status]@row = "Not delivered", "Not delivered", IF(AND(ISBLANK([COURSE START DATE (TERM SHEET)]@row), ISBLANK([ONBOARDING COMPLETION DATE]@row)), "SD Missing", IF(ISBLANK([ONBOARDING COMPLETION DATE]@row), "", IF(ISBLANK([COURSE START DATE (TERM SHEET)]@row), "SD Missing", IF([ONBOARDING COMPLETION DATE]@row <= [COURSE START DATE (TERM SHEET)]@row, "On Time", IF([ONBOARDING COMPLETION DATE]@row > [COURSE START DATE (TERM SHEET)]@row, "Delayed")))))))

Output:

The "Test" in the Onboarding Status is purely so the formula doesn't just see the line as blank and ignore it.

Hope this helps!

Nick Korna

@MJ_1 ,

You can prioritise the criteria based on their order in the big nested IF formula. If the first IF criteria is fulfilled, it will display the result given for true. If not, it will move onto the false result, which in this case is another IF check. This can continue for a while - hence the long formula!

The formula should already display 'N/A' if the status is changed to 'Not requested', regardless of any filled dates as this is the first check the nested IF statement is checking for.

Example:

With Onboarding status changed to Not requested:

If you wanted the dates to be cleared if Onboarding Status was changed to "Not requested", this could be done via automation to save it needing to be manually done:

You can move the order of the IF statements round if you want a different result, but you need to be careful that an earlier one would not fulfil a different set of criteria and thus display a different result to what you're after. For example, if you moved the

IF([Onboarding Status]@row = "Not requested", "N/A"

section to be the last chain in the formula, it would effectively end up ignored because the "SD Missing"/blank criteria could be fulfilled first (due to dates not needing to be filled in) and this is what would be displayed instead - assuming the automation above was not used.

Hope this makes sense - if you've more questions then just ask! 😊

MJ_1

One more question, if I have a formula to achieve these, and also need a fill colour, is it best to use conditional formatting to say if OKR column shows SD missing, fill cell red OR can that be captured in the formula?

Nick Korna

Hi @MJ_1 ,

If I'm understanding you correctly this formula should do what you're after:

=IF(ISBLANK([ONBOARDING COMPLETION DATE]@row), "", IF(ISBLANK([COURSE START DATE (TERM SHEET)]@row), "SD Missing", IF([ONBOARDING COMPLETION DATE]@row <= [COURSE START DATE (TERM SHEET)]@row, "On Time", IF([ONBOARDING COMPLETION DATE]@row > [COURSE START DATE (TERM SHEET)]@row, "Delayed"))))

Example data:

For the fill colour I would use conditional formatting to get the desired result. I'm not aware of any way to do this via cell/column formula.

Hope this helps - if you've any questions etc. then just ask! 😊

MJ_1

Thanks so much @Nick Korna The formula is exactly what I needed and is now working. I have a few more scenarios I have not taken into consideration in the overall formula, if you could kindly help, and add them to the formula above.

• If Course Start Date Term Sheet is blank and the Onboarding Completion Date is blank, OKR formula column to say SD Missing, as the Course Start Date is the one that is to be read as higher priority to inform the OKR formula column.
• I have another column on the Smartsheet, Onboarding Status. If the Onboarding Status shows Not Requested, then the OKR formula column needs to say N/A.
• If the Onboarding Status shows Not Delivered, the OKR column needs to say Not Delivered.

Thanks for advice!

Nick Korna

@MJ_1,

This should do what you're after:

=IF([Onboarding Status]@row = "Not requested", "N/A", IF([Onboarding Status]@row = "Not delivered", "Not delivered", IF(AND(ISBLANK([COURSE START DATE (TERM SHEET)]@row), ISBLANK([ONBOARDING COMPLETION DATE]@row)), "SD Missing", IF(ISBLANK([ONBOARDING COMPLETION DATE]@row), "", IF(ISBLANK([COURSE START DATE (TERM SHEET)]@row), "SD Missing", IF([ONBOARDING COMPLETION DATE]@row <= [COURSE START DATE (TERM SHEET)]@row, "On Time", IF([ONBOARDING COMPLETION DATE]@row > [COURSE START DATE (TERM SHEET)]@row, "Delayed")))))))

Output:

The "Test" in the Onboarding Status is purely so the formula doesn't just see the line as blank and ignore it.

Hope this helps!

MJ_1

@Nick Korna This is great, thanks so much!

I am wondering if there is a way to prioritise one of the formula criterion over another?

For example, with the current formula, if the Onboarding Completion Date is less than or = to the Course Start Date, and the dates are both filled in the formula column will show 'On Time' which is exactly what I needed, but if the rep then changes the Onboarding Status to Not Requested, how can I get the OKR Formula column to use the Status of Not Requested so this shows 'N/A' in the formula column instead of ' On Time' for this scenario?

Appreciate all your advice so far. I am very new to formulas so this is very helpful.

Thanks!

Nick Korna

@MJ_1 ,

You can prioritise the criteria based on their order in the big nested IF formula. If the first IF criteria is fulfilled, it will display the result given for true. If not, it will move onto the false result, which in this case is another IF check. This can continue for a while - hence the long formula!

The formula should already display 'N/A' if the status is changed to 'Not requested', regardless of any filled dates as this is the first check the nested IF statement is checking for.

Example:

With Onboarding status changed to Not requested:

If you wanted the dates to be cleared if Onboarding Status was changed to "Not requested", this could be done via automation to save it needing to be manually done:

You can move the order of the IF statements round if you want a different result, but you need to be careful that an earlier one would not fulfil a different set of criteria and thus display a different result to what you're after. For example, if you moved the

IF([Onboarding Status]@row = "Not requested", "N/A"

section to be the last chain in the formula, it would effectively end up ignored because the "SD Missing"/blank criteria could be fulfilled first (due to dates not needing to be filled in) and this is what would be displayed instead - assuming the automation above was not used.

Hope this makes sense - if you've more questions then just ask! 😊