Duplicate Dates Across Multiple Columns

Options

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.

Tags:

Best Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    James Keuning ✭✭✭✭✭
    Answer ✓
    Options

    @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

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    James Keuning ✭✭✭✭✭
    Answer ✓
    Options

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!