Identify duplicate dates and exclude blank cells

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!

Meeting Schedule SmartSheet.png

Find more posts tagged with

Formulas

Solution Center

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
How can I set up recurring tasks in Smartsheet?
I need tasks that repeat (daily, weekly, monthly, yearly). Each should show a due date and a “Completed” checkbox. When I check it off, I’d like the task to log in an archive sheet with the completed date and then reset on the main sheet with a new due date and cleared checkbox. Has anyone found a simple way to do this?
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…