CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jason
Jason

Reputation: 52523

How do I conditionally format a date that falls between two dates from a list of start and end dates?

I have created a dynamic calendar in a Google Sheets document and with it a list of dates:

Screencap of spreadsheet

The days are dynamically populated with the full date but formatted to only show the day (for example, the full value of August 22nd is 8/22/2020 but it is formatted to only show 22). I would like to use conditional formatting to highlight the date ranges listed on the right. For instance, I would simply like the dates 8/12, 8/13, and 8/14 (in addition to the other date ranges in those columns) to be highlighted using conditional formatting.

I've been able to get single ranges highlighted by directly targeting the I and J cells in the range A3:G8 like so:

=AND(A3>=$I$2,A3<=$J$2) or by switching up the column value. Removing any $ symbol breaks the conditional formatting.

I know this is not correct to get each range in the list to appear, but this is the only way I could get any ranges to work. Obviously I don't want to go one by one, but nothing else I've tried has worked. I've spent the last 5 hours scouring the internet and have come up with nothing like this problem. I have tried too many things to list here, and nothing has worked.

FWIW, this is my test data set. My full data set is much larger, is not sorted by start date, and has some start dates missing. I could potentially clean up the missing start dates if necessary, but my final dataset can't be sorted.

Ideally, the final product should look something like this:

Screencap of correct spreadsheet

Upvotes: 1

Views: 1058

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

Try

=SUMPRODUCT((A1<=$J$2:$J)*(A1>=$I$2:$I)*(A1<>""))>0

enter image description here

You can add as many rows as you wish.

Explanation

In this formula, you have 3 conditions

=SUMPRODUCT(condition1 * condition2 * condition3)
  • If one condition is false, you will get 0 (for instance truefalsetrue = 0 )
  • If all three conditions are true, you will get something > 0, that means that the date is not null and between a range of dates

Upvotes: 1

Related Questions