Need help with date range overlap formula

Please see attached screenshot. I use the "Duplicate" column to show whether or not there is an overlap in the date range between new entries and the "Start Date" & "End Date" columns. In Excel, the following formula works great:

=SUMPRODUCT((D2<enddate)*(E2>=startdate))>1

I know that Smartsheet doesn't support SUMPRODUCT. I'm hoping someone can help with an alternative.

Thanks for considering!

9-29-2017 3-27-20 PM.jpg

9-29-2017 3-34-14 PM.jpg

Find more posts tagged with

Formulas

Government

IT & Operations

Comments

Shaine Greenwood

Hello,

You're correct in that there currently isn't a SUMPRODUCT function in Smartsheet at this time. There also currently isn't a way to compare single date values with ranges of values (or create named ranges).

You might try using an IF statement combined with MAX and MIN instead:

=IF([AND(Start Date]1 < MAX([End Date]:[End Date]), [End Date]1 >= MIN([Start Date]:[Start Date])), 1)

I'm hoping this does what you're looking for. Let me know if it doesn't and I'll brainstorm again.

Dartwohl

Hi Shaine.

Thanks for your response. The "reply" button isn't working so I hope you'll still see this.

Your formula seems to check the "Overlap" box no matter what date range I add. Just to be clear, I'm hoping to have that box checked ONLY if there is a conflict (overlap) with other rows on the sheet.

Any other ideas? Thanks in advance!

Dustin

J. Craig Williams

This is what I use for checking duplicates:

=IF(COUNTIF(Node:Node, Node23) > 1, 1)

This checks if the cell in Node23 matches the contents of the rest of the [Node] column and if so, returns a 1.

In a checkbox column, this would results in the box being checked.

This might be expanded to include your date range, but I haven't done the work yet.

If SUMPRODUCT will work (I have not tried it with dates), then this is the SUMPRODUCT in Smartsheet:

=AVGW(array of numbers, array of weights) * SUM (array of weights)

I hope this helps.

Craig

Dartwohl

Hi Craig.

Thanks for your reply. For determining whether or not there's a duplicate entry, I'm using the following and it appears to be working great. =COUNTIF([ColumnA]:[ColumnA], [ColumnA]1) > 1

Still stuck on date overlap though...

I'm not sure your AVGW formula will do the trick, unfortunately. Is your intent to have the cell in 'Overlap' column checked if there's a date overlap anywhere in the range between "Start Date" and "End Date" columns? The SUMPRODUCT excel formula I posted originally does this perfectly.

Let me know if this isn't clear.

Thanks,

Dustin

J. Craig Williams

I have not solved the overlapping dates either.

I was hoping the new COLLECT() function might get us there, but if it does, I haven't discovered it.

What is lacking is some sort of matrix mathematics, which Smartsheet is lacking. This could be done using the API, I would imagine.

Craig