How do I set a condition based on range of difference between dates?

John Cardoulis - Gmail

I am trying to set an action based on the range of difference between dates. If the END DATE is 5 DAYS OR MORE than the START DATE, take an action.

Paul Newcome

Are you trying to set up an automation or have a formula create an output on the sheet?

John Cardoulis - Gmail

I have a single sheet, multiple rows. Each row is an event. I display the events in calendar format. I want to change the colour of the whole line when an end date is 5 days more than a start date.

Andrée Starå

Hi @John Cardoulis - Gmail

I hope you're well and safe!

Add a so-called helper column which we'll use to check the difference in days between the dates.

Something like this. (can be converted to a Column Formula)

=[End Date]@row - [Start Date]@row

The next step would be to set up the Conditional Formatting Rule to check for the number of days in that column.

Make sense?

Did that work/help?

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!

John Cardoulis - Gmail

Thank you for this. I understand the logic here. I do not know how to enter this formula. I have only used the conditional formatting dropdown, where I select fields and arguments. Where/how do I enter a free-form formula? Thanks again.

Paul Newcome

You would enter the formula into a new column.

John Cardoulis - Gmail

I am sorry. Not getting this. Where do I enter the formula. I can't enter it within the conditional formatting dropdown. Do I enter it as data in the column?

John Cardoulis - Gmail

Never mind. Figured it out. And it works. Thank you.

Paul Newcome

Happy to help. 👍️

Andrée Starå

@John Cardoulis - Gmail

Excellent!

Happy to help!

✅Remember! 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!