# How To Compare Dates

Options

Why does this:

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

return #UNPARSEABLE?

And this:

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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.

Alan

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

Yes,

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

-Alan

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• ✭✭✭✭✭✭
Options

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!