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
-
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.
-
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!
Answers
-
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.
-
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
-
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!
-
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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!