How To Compare Dates

Alan Praysman

Why does this:

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

return #UNPARSEABLE?

And this:

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

Andrée Starå

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.

Paul Newcome

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

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!

Andrée Starå

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.

Alan Praysman

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

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

Andrée Starå

@Alan Praysman

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Alan Praysman

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

Happy to help! 👍️

Andrée Starå

@Alan Praysman

Excellent!

You're more than welcome!