IF command and Date

VioletB

Hi,

 I have 2 columns: Next Audit Due Date and Status

If the date in ‘Next Audit Due Date’ column is greater than the current date, I would like the ‘Status’ column to fill in green and return value of On Track.

If the date in ‘Next Audit Due Date’ column is less than the current date, I would like the ‘Status’ column to fill in red and return value of Overdue

Thanks for your help.

Andrée Starå

Hi Violet,

Try something like this.

Use the formula below and then add the Conditional Formatting rules as needed from the status.

=IF(ISBLANK([Next Audit Due Date]@row); ""; IF([Next Audit Due Date]@row < TODAY(); "Overdue"; IF([Next Audit Due Date]@row > TODAY(); "On Track")))

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

=IF(ISBLANK([Next Audit Due Date]@row), "", IF([Next Audit Due Date]@row < TODAY(), "Overdue", IF([Next Audit Due Date]@row > TODAY(), "On Track")))

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

Did it work?

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD