Issue with Collect formula

L_123

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

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