Stuck on very basic formula

Molly12345

Hi everyone,

I have no real knowledge of excel formulas and smartsheet functions so excuse the ignorance here.

I am stuck on a basic formula for outstanding:

I need it to say outstanding if the completed date is empty but passed the tolerance end date. I need it to stay blank if is completed by the end of tolerance.

Many thanks in advance 😀

Nick Korna

Hi @Molly12345 ,

For your outstanding column this formula should do the trick:

=IF(AND(ISBLANK([Completed Date]@row), ([Tolerance END]@row < TODAY())), "Outstanding", "")

And a formula for your Completed by end of Tolerance:

=IF(ISBLANK([Completed Date]@row), "", IF([Completed Date]@row < [Tolerance END]@row, "Yes", "No"))

This will calculate if the task was completed on time ("Yes"), later than it should've been ("No") or remain blank if no completion date has yet been filled in.

Example data for both:

Hope these help you out. If I've got something wrong or you've any questions etc. then just post! 😊

Molly12345

Hi Nick,

Brilliant thank you so much, I appreciate that they could be copied and pasted directly in !

For some strange reason, 3 rows that are completely blank show up as outstanding on one of my sheets. However the rows below do not and they are also blank.

Many thanks in advance

Molly12345

Appologies , when I convert to the column formula everything blank says outstanding. I forgot to say when the tolerance end is blank to leave the cell blank also! Thanks in advance !

Molly12345

i Tried =IF(OR(AND(ISBLANK([Completed Date]@row), TODAY() > [END]@row), [Completed Date]@row > [END]@row), "OVERDUE", " ") + IF([END]@row = "", "") , however when I convert to column formula it still leaves 3 random empty rows as OVERDUE and the rows below empty, for some strange reason! Any advice would be greatly appreciated