Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Need help with date range overlap formula

Dartwohl
Dartwohl ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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

Comments

  • 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
    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
    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
    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
    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

This discussion has been closed.