Issue with Collect formula

I am having an issue with a formula that I believe to be a bug.

=LEFT(JOIN(COLLECT($C:$C,$Created:$Created, NETDAYS(@cell, TODAY()) <= IF(WEEKDAY(TODAY()) = 7, 2, IF(WEEKDAY(TODAY()) = 6, 1, WEEKDAY(TODAY()) + 2)), $A:$A, $[Work Description]@row)))

Does not work, it propogates an error in the cell. If I rearrange the inputs inside of the collect it seems to work though.

=LEFT(JOIN(COLLECT($C:$C, $A:$A, $[Work Description]@row, $Created:$Created, NETDAYS(@cell, TODAY()) <= IF(WEEKDAY(TODAY()) = 7, 2, IF(WEEKDAY(TODAY()) = 6, 1, WEEKDAY(TODAY()) + 2)))))

To me there shouldn't be any difference between the two formulas. I obviously have a solution that works, but it is causing me to have to do quite a bit of transcribing formulas from the one to the other formula for unexpected behaviour

Find more posts tagged with

Formulas

Comments

J. Craig Williams

That seems like a complicated way to return a single letter, but I'm sure there is a good reason for it.

Tossing an IFERROR around the NETDAYS() calc

=LEFT(JOIN(COLLECT($C:$C, $Created:$Created, IFERROR(NETDAYS(@cell, TODAY()), 13) <= IF(WEEKDAY(TODAY()) = 7, 2, IF(WEEKDAY(TODAY()) = 6, 1, WEEKDAY(TODAY()) + 2)), $A:$A, $[Work Description]@row)))

at least points to where the error lies. Odd one.

Craig

L_123

the main point of the formula is the date parsing. It is a tracking form that keeps track of the last 10 weeks, and each week moves back as time progresses. people submit their actions via webform, and the formulas tell out of the last 10 weeks how many have been completed. This formula is for the current week, and just returns a C if they completed it or a N if they were not available.

Everyone gets an update request every friday and click on a webform link instead of the button.

This saves me from having to update the dates, or have to work on the sheet almost at all. Only maintenance is deleting some of the rows when it gets to long. and nobody can go back and edit the submission time/dates as it is in a date/time created column, not even me, which means I don't get pestered to change stuff. (Well, not as much)

It was working very well for one department and when I adapted it for another department I found this issue.

J. Craig Williams

Interesting

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…