How to setup conditional formatting comparing dates between two cells

Mix Denmark

I'm looking for a way to setup a conditional formatting (I assume) comparing the dates in two columns in the same row.

Specifically, I'm working with "planned material receive dates" and "planned work dates". If the "planned work date" is before the "material receive date" in the same row, I would like the "planned work date" to be red, to make sure I don't plan the work before the material is planned to arrive.

Can anybody think of a way to do this?

Thanks!

Andrée Starå

Hi @Mix Denmark

I hope you're well and safe!

Yes, but you need to add a so-called helper column with a formula that checks the dates and then use that in the Conditional Logic rule.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

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

✅Did my post(s) 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å

Godmorgen! @Mix Denmark

Happy to help!

Try something like this. (you can add it to a checkbox column)

=IF(OR([Planned material receive date]@row = ""; [Planned work date]@row = ""); ""; IF([Planned work date]@row < [Planned material receive date]@row; 1))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support 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å

Hi @Mix Denmark

I hope you're well and safe!

Yes, but you need to add a so-called helper column with a formula that checks the dates and then use that in the Conditional Logic rule.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

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

✅Did my post(s) 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!

Mix Denmark

Hi Andrée.

Thank you for your quick answer! Could you give me a hint to how that formula should be written out? I would like it to be "if (work date) is equal to or before (receive date)".

Andrée Starå

Godmorgen! @Mix Denmark

Happy to help!

Try something like this. (you can add it to a checkbox column)

=IF(OR([Planned material receive date]@row = ""; [Planned work date]@row = ""); ""; IF([Planned work date]@row < [Planned material receive date]@row; 1))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support 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!

Mix Denmark

Godmorgen Andrée :)

It works! Thank you so much.

Andrée Starå

@Mix Denmark

Excellent!

You're more than welcome!

Walt L

Hi.

I am curious about the same kind of formula & conditional formatting. My situation is: I have 2 columns for dates; they could be the same date, or, they could be different dates. I am trying to figure out how to set up a conditional format that will highlight the cell w/ a different date, calling that out to my team. Any help would be appreciated.

Genevieve P.

Hi @Walt L

I would suggest using a checkbox column that checks the box if the date is not = the other date.

=IF([Date one]@row = [Date two]@row, 0, 1)

Then you can hide this column in your sheet, but use the checkbox as the rule for your Conditional Formatting.

Cheers,

Genevieve

mtp105

For those that tried Andree's answer, you may need to switch from a semicolon (;) to a comma (,). That worked for me.