Need help with date range overlap formula

Dartwohl

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!

 

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