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
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!
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.
-
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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives