Comparisons of dates(Committed date,New Committed date & Actual Completion Date)

Khasim

Hello I have 3 dates column in smart sheet i.e Committed date,New Committed date & Actual Completion Date

I need to compare these dates like if Commited date is> New committed date then i should get some color & the same way if committed date>actual completion date then i should get color & the same if New committed date is date then i should get one color.

Please help me with the formula?

Andrée Starå

Hi,

You would need to use so-called helper columns for this in combination with Conditional Formatting.

In my example, I use two new checkbox columns for the different criteria that we'd use in the conditional formatting.

Try something like this.

Commited date is> New committed date (Helper Column 1)

=IF(ISBLANK([New Committed Date]@row); 0; IF([Committed Date]@row > [New Committed Date]@row; 1))

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

=IF(ISBLANK([New Committed Date]@row), 0, IF([Committed Date]@row > [New Committed Date]@row, 1))

committed date>actual completion date (Helper Column 2)

=IF(ISBLANK([Actual Completion Date]@row); 0; IF([Committed Date]@row > [Actual Completion Date]@row; 1))

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

=IF(ISBLANK([Actual Completion Date]@row), 0, IF([Committed Date]@row > [Actual Completion Date]@row, 1))

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

New committed date is date

This one we don't need a helper column for. We can set this up in the Conditional Formatting.

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help 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!