How To Compare Dates

Why does this:

=if([End Date]7=TODAY,"Yes","no")

return #UNPARSEABLE?

And this:

-if(and([End Date]7<TODAY, [%Complete]<100),"RED", "YELLOW")

Best Answers

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Alan,

    There are primarily two errors in the formulas. You'll have to have the TODAY function with parenthesis like, TODAY() and the %Complete should be 1 and not 100 because 1 = 100% in Smartsheet.

    Smartsheet looks at the numbers in a column formatted for percentage (for example, a % Complete column) as values between 0 and 1. Use decimal values (examples: 0.25 for 25%, 0.5 for 50%) to get the correct comparison, as opposed to whole numbers.

    Try something like this.

    I've added the @row function so you don't need to worry about the row numbers.

    Formula 1

    =IF([End Date]@row = TODAY(); "Yes"; "No")

    The same version but with the below changes for your and others convenience.

    =IF([End Date]@row = TODAY(), "Yes", "No")
    

    Formula 2

    =IF(AND([End Date]@row < TODAY(); [%Complete]@row < 1); "RED"; "YELLOW")

    The same version but with the below changes for your and others convenience.

    =IF(AND([End Date]@row < TODAY(), [%Complete]@row < 1), "RED", "YELLOW")
    

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    To add.

    I'd recommend adding an ISBLANK function to the formulas so it doesn't show any values if the date cells are blank. Let me know if you also need it for the %Complete.

    Try something like this.

    Formula 1

    =IF(ISBLANK([End Date]@row), "", IF([End Date]@row = TODAY(), "Yes", "No"))

    Formula 2

    =IF(ISBLANK([End Date]@row), "", IF(AND([End Date]@row < TODAY(), [%Complete]@row < 1), "RED", "YELLOW"))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Andree,

    Thank you for advice, I think I'm making progress.

    I'm trying to populate a column, which is set as Symbols: R,Y,G

    =IF(AND([End Date]@row < TODAY(), [%Complete]@row < 1), "RED", "YELLOW") - this works using TODAY(),
    but returns verbatim, and not the expected symbols; 
    

    =IF(ISBLANK([End Date]@row), "", IF(AND([End Date]@row < TODAY(), [%Complete]@row < 1), "RED", "YELLOW")) - returns #UNP...

    I'm doing this on the project imported from MS Project file. Not sure if it has anything to do wit this. The country is US.

    Thank you for your help.

    Alan







  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The colors are case sensitive. "Red" and "Yellow".

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Alan Praysman

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée 

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Yes,

    Red/Yellow did the trick. Many thanks to both of you - Andree an Paul for your help. I'll spread the knowledge.

    -Alan

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Alan Praysman

    Excellent!

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!