Help with formula issue not comparing correct data values

Justin326326

Hi, so this formula used in Column "1" seems to actually only be finding the network days between the current rows Target Start and End, instead of finding the nework days between the max between current rows target start and the first indexed value of start dates that meet a criteria, and the min between current rows target end and the first indexed value of end dates that meet a criteria

These columns 1,2,3 etc. would be summed up in "Total Overlap Days"

=IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX([Target Start]@row, INDEX([Target Start]:[Target Start], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 1))), MIN([Target End]@row, INDEX([Target End]:[Target End], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 1)))), 0))

Georgie

Hi @Justin326326,

It looks like the MAX and MIN functions are set up correctly in your formula and the issue might be within the second dates within the MAX and MIN functions that are returned using the INDEX(COLLECT) section. 

As we can’t see what your Assigned To Helper column is doing, it’s difficult for us to troubleshoot this on our end. However, if you pull out the INDEX(COLLECT) section and change this to a JOIN(COLLECT), you’ll be able to see all the Row IDs that this section is collecting for each row. You can then check if these are the expected row IDs - if they aren’t, then you’ll need to figure out why it’s collecting those row IDs and not the expected ones. 

I’d suggest adding a new column just for troubleshooting purposes to do this (which can be deleted once we’ve determined the issue). You can enter the formula below in that new column, as this is your INDEX(COLLECT) section changed to a JOIN(COLLECT):

• =JOIN(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), ” / “) 

If you’re still having trouble figuring out the issue, could you share screenshots of the Row IDs that are collected with the above formula, provide the row IDs you’d expect to be returned if the wrong ones are being returned,, as well as a written description of the criteria that rows need to meet, and a view of the formula used in the Assigned To Helper column?

Hope this helps!

Georgie

Justin326326

Hi @Georgie thank you for the reply,

First of all I do actually have a column with JOIN(COLLECT()) used in the example shown here, and I can confirm that it returns the correct outputs, so I do not feel as if that is the issue

So, in the origional image you can see these overlapping rows displayed in the column "Overlap Rows" and they return the corresponding Row IDs to check.

"Overlap Rows Count" is just a sum of all the number columns that count the number of overlapping days between rows.

If there is anything else I could provide please let me know

Justin326326

@Georgie So, in the row 306 (light blue) you can see that this row overlaps with row 238, in column "1" it states that it overlaps with the current row over an 11 day duration

In row 238 it states that it overlaps with row 306, which would be correct.

However, when we look at the date values row 238 is 10/03/25-14/03/25, whilst row 306 is 14/03/25-28/03/25

Now, clearly there is actually only a 1 day overlap here, however as can be seen from their respective results row 306 outputs 11, which are the working days between its start and end date. While 5 days for row 238 would be the number of working days between its start and end date

Just, providing this screenshot to remind how the formula is calculated here.

Georgie

Hi @Justin326326,

Thanks for confirming that the JOIN(COLLECT) in the Overlap Rows column is returning the correct row IDs. 

I’m assuming that columns 2, 3, 4 and 5 all have similar formulas? If that’s correct, are those columns returning the correct values? It’s hard for me to tell without seeing the entire sheet since I can’t see all rows with their row IDs and dates. If those are working correctly, I’d try re-writing your formula in column 1 based on the formulas in those columns.

If that doesn’t help resolve the issue, I’d recommend opening a support ticket or booking a Pro Desk session, if you’re eligible, so that a specialist from the relevant team can take a look at your setup and assist with possible solutions. 

Hope that helps!

Georgie

Justin326326

@Georgie Hi Georgie, I have an enterprise support plan, does a Pro Desk session come free and unlimited? As I was offering to do this task to help one collegue, so would not feel using up a pro-desk session would make sense if I have limited appointments I can make.

To answer your question regarding the other columns, yes they have the same formula, however they index the 1st, 2nd, 3rd, 4th rowID etc that matches criteria.

I made a mistake earlier as "Overlap Rows Count" actually just tells you how many rows overlap and thus how many values would be indexed. So, if I had 12 overlapping rows, I would need to make 12 columns of (1,2,3 etc.)

I will just enter here the formulas to column 1 and 2, so you get an idea of how it works

=IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX([Target Start]@row, INDEX([Target Start]:[Target Start], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 1))), MIN([Target End]@row, INDEX([Target End]:[Target End], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 1)))), 0))

=IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX([Target Start]@row, INDEX([Target Start]:[Target Start], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 2))), MIN([Target End]@row, INDEX([Target End]:[Target End], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 2)))), 0))

The RowID is in this image, so are the date values.

Georgie

Hi @Justin326326,

You can check out what’s included in different support plans here: Overview of Smartsheet support resources. 

If you’re on the Professional or Premium Support package, Pro Desk sessions are included and unlimited and can be used as troubleshooting sessions. If you’re on these support packages, you will also have 24/7 phone support and web-based ticketing support through the customer support portal. Phone support and Pro Desk sessions would provide you with the opportunity to share your screen with a specialist who can take a good look at your sheets and carry out troubleshooting live with you, then determine the next best steps.  

Hope that helps!

Georgie