Duplicate Dates Across Multiple Columns

Graham Labiche

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.

L_123

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.

James Keuning

@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:

L_123

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.

James Keuning

@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: