Quick look at a function

I'm trying to have the Status column drop down pick "Processing" if date is after today's date & trying to have the Status column drop down pick "LATE" if date is before today's date.

But if the date is blank, what would need to be added to the function to have the drop down not choose anything?

All the help is greatly appreicated

Best Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 05/12/22 Answer ✓

    @Christopher Warren

    You need to add the IF blank first, like so:

    =IF(ISBLANK([Estimated Completion Date]@row), "", IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row >= TODAY(), "Processing", "")))

    I also changed your "Processing" slightly because without the "or equal to today" part it was going to want to leave an Estimated Completion Date of today blank as well. If you want a date of today marked as "LATE" instead of "Processing" then put the = in the Late formula instead and remove it from the Processing one.

    Also, this formula wipes out your ability to have anything set to Not Started. Maybe you should have it choose Not Started if the Estimated Completion Date is blank? If so, in the empty quotes near the start of the formula put Not Started in there instead of ""

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Christopher Warren You only need that ISDATE at the very beginning:

    =IF(ISDATE([Estimated Completion Date]@row), IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row > TODAY(), "Processing")), "")

    If it's a date, evaluate if it's after or before today and set the status value, if it's not a date, leave the status value blank.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 05/12/22 Answer ✓

    @Christopher Warren

    You need to add the IF blank first, like so:

    =IF(ISBLANK([Estimated Completion Date]@row), "", IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row >= TODAY(), "Processing", "")))

    I also changed your "Processing" slightly because without the "or equal to today" part it was going to want to leave an Estimated Completion Date of today blank as well. If you want a date of today marked as "LATE" instead of "Processing" then put the = in the Late formula instead and remove it from the Processing one.

    Also, this formula wipes out your ability to have anything set to Not Started. Maybe you should have it choose Not Started if the Estimated Completion Date is blank? If so, in the empty quotes near the start of the formula put Not Started in there instead of ""

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Christopher Warren You only need that ISDATE at the very beginning:

    =IF(ISDATE([Estimated Completion Date]@row), IF([Estimated Completion Date]@row < TODAY(), "LATE", IF([Estimated Completion Date]@row > TODAY(), "Processing")), "")

    If it's a date, evaluate if it's after or before today and set the status value, if it's not a date, leave the status value blank.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!