# Identify duplicate dates and exclude blank cells

Options
✭✭
edited 12/09/19

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!

• Employee
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!