Identify duplicate dates and exclude blank cells

SB017

I am working with a SmartSheet that is used by multiple people to schedule meetings.  I am trying to check for duplicate meeting dates within two columns "Proposed Date" and "Proposed End (if multiple days are needed)".  Dates are entered in rows 7-28.  I have added a checkbox column titled "Duplicate Date" to be checked if a duplicate date is detected.  The original formula that I tried is:

=IF(COUNTIF([Proposed Date]7:[Proposed End (if multiple days are needed)]28, [Proposed Date]7) > 1, 1) + IF(COUNTIF([Proposed Date]7:[Proposed End (if multiple days are needed)]28, [Proposed End (if multiple days are needed)]7) > 1, 1)

I am running into two problems that I can see so far with the formula.  1)  It is considering blank cells duplicates and I need to exclude those.  2).  When I have a date range such as Proposed Date is 02/10/20 and Proposed End is 02/12/20, it does not identify 02/11/20 as a duplicate date.

What would be the best way to identify any duplicate dates or date ranges in the columns and exclude any blank cells?  Thank you!

 

Sean Morgan

Hllo SB017,

 

Thank you for reaching out within the Community. I have tested a solution on my sheet and have had success using the following formula.

 

Please note that this is an example and will require modification to work within your sheets:

=IF(ISBLANK(Start@row), “”, IF(ISBLANK(Finish@row), “”, IF((COUNTIFS(Finish:Finish, <=Finish@row, Finish:Finish, >=Start@row) + COUNTIFS(Start:Start, >=Start@row, Start:Start, <=Finish@row) + COUNTIFS(Start:Start, <=Start@row, Finish:Finish, >=Start@row) - 3) > 0, 1, 0)))

 

The " =IF(ISBLANK(Start@row), “”, IF(ISBLANK(Finish@row), “” " part of the formula checks that if either the Start or Finish cells are empty, it will leave it as an empty cell / enter a blank string text.

 

This part of the formula "IF((COUNTIFS(Finish:Finish, <=Finish@row, Finish:Finish, >=Start@row) + COUNTIFS(Start:Start, >=Start@row, Start:Start, <=Finish@row) + COUNTIFS(Start:Start, <=Start@row, Finish:Finish, >=Start@row) - 3) > 0, 1, 0" identifies the values between the Start and Finish date range to which it will mark a duplicate within the checkbox column if the same value within that date range is found elsewhere.

 

I hope this helps resolve your inquiry. Thank you for using Smartsheet Community!

 

Kindest Regards

 

Sean