Duplicate Dates Across Multiple Columns
Hello,
I'm trying to highlight duplicate dates with conditional formatting across various columns to avoid conflicts.
Is there a simple way to do this?
Sample Data is below.
Thanks in advance.
Best Answers

You can add a column with a simple formula
=if(countif(date:date,date@row)>1,1,0)
Then use that column to conditionally format your date column based on if the result in your helper column is a 1 or a 0. Then hide the helper column so it doesn't expand your sheet out.

@L@123 provides a graceful solution, but you will need that formula in one column per date column that you are testing. I recommend setting this up with your date columns named Date1, Date2, etc and the testing columns as 1, 2 etc, and then you change the column names once everything is working.
The formulas for testing column 1,2 etc look like this:
=IF(COUNTIF([Date1]@row:[Date4]@row, [Date1]@row) > 1, 1, 0)
=IF(COUNTIF([Date1]@row:[Date4]@row, [Date2]@row) > 1, 1, 0)
So all you need to do is change the name of the criterion field
Then the conditional formatting looks like:
You can use the clone feature to make the rules faster.
In the end you will end up with this:
Answers

You can add a column with a simple formula
=if(countif(date:date,date@row)>1,1,0)
Then use that column to conditionally format your date column based on if the result in your helper column is a 1 or a 0. Then hide the helper column so it doesn't expand your sheet out.

@L@123 provides a graceful solution, but you will need that formula in one column per date column that you are testing. I recommend setting this up with your date columns named Date1, Date2, etc and the testing columns as 1, 2 etc, and then you change the column names once everything is working.
The formulas for testing column 1,2 etc look like this:
=IF(COUNTIF([Date1]@row:[Date4]@row, [Date1]@row) > 1, 1, 0)
=IF(COUNTIF([Date1]@row:[Date4]@row, [Date2]@row) > 1, 1, 0)
So all you need to do is change the name of the criterion field
Then the conditional formatting looks like:
You can use the clone feature to make the rules faster.
In the end you will end up with this:
Help Article Resources
Categories
Check out the Formula Handbook template!