What's wrong with this formula?

Phil Wightman

=IF(Due date,<=Date closed, "yes", "[No]")

the intent is to record yes or no in a helper column dependent. The former if the date closed is on or before the due date. The latter if after the due date.

Thanks in advance

Paul Newcome

Try this:

=IF([Due date]@row<=[Date closed]@row, "yes", "[No]")

Phil Wightman

Thanks @Paul Newcome that works. A further question, what would I add to the formula so that the cell stays blank until both dates are entered? ATM when I add it to the column it shows 'No' for blank rows (ie not met). Just to keep it tidy.

Paul Newcome

You would need somethign more like this:

=IF(AND([Due Date]@row <> "", [Date Closed]@row <> "", [Due date]@row<=[Date closed]@row), "yes", "[No]")

Phil Wightman

Hi @Paul Newcome . This did not remove a yes or no when other columns are blank. Any further suggestions Tx

Andrée Starå

Hi @Phil Wightman

I hope you're well and safe!

Try something like this.

=
IF(OR([Due Date]@row = "", [Date Closed]@row = ""), "", 
IF(AND([Due Date]@row <> "", [Date Closed]@row <> "", 
[Due Date]@row <= [Date Closed]@row), "yes", "[No]"))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Phil Wightman

Hi @Andrée Starå

That worked! Thanks. One further query, if i want it to be less than or = to by adding < it seems to screw it up. This woudl be helpful as with only the = sign it says its late if both dates are the same when in reality if its closed on the date planed its is on time. Only after that date is it late.

Andrée Starå

@Phil Wightman

Excellent!

Happy to help!

Try removing the = and only keep the <.

=IF(OR([Due Date]@row = "", [Date Closed]@row = ""), "", IF(AND([Due Date]@row <> "", [Date Closed]@row <> "", [Due Date]@row < [Date Closed]@row), "yes", "[No]"))

Did that work?

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!