Need help with formula for individual non-workdays

Gina Minard-Rivera

Hello,

I am using the following formula in my project timeline and which references another sheet which logs any individual non-work dates. This works great in most circumstance:

=IF(OR(COUNTIFS({Sheet - OneStream Non-Working Days Assigned to}, [Assigned To]@row, {Sheet - OneStream Non-Working Days Start Date}, >=[Start Date]@row, {Sheet - OneStream Non-Working Days Range 1}, <=[End Date]@row) > 0, COUNTIFS({Sheet - OneStream Non-Working Days Assigned to}, [Assigned To]@row, {Sheet - OneStream Non-Working Days Start Date}, <=[Start Date]@row, {Sheet - OneStream Non-Working Days Range 1}, >=[End Date]@row) > 0), "Unavailable", "Available")

Using the formula logic, I THOUGHT the results would be as such:

Resource Start Date 10/15/2020 >= Timeline Start Date 10/26/2020 no, so count = 0

Resource End Date 10/30/2020 <= Timeline End Date 10/30/2020 yes, so count = 1

Since Count is greater than 0, the formula proceeds

Resource Start Date 10/15/2020 <= Timeline Start Date 10/26/2020 yes, so count = 1

Resource End Date 10/30/2020 >= Timeline End Date 10/30/2020 yes, so count = 1

Since count is greater than 0, results should be Unavailable. However, I am getting results of zero on everything and ultimately Available which is incorrect. As my timeline has almost a thousand lines, I really need this to be accurate. What am I doing wrong?

Thanks for your help.

Paul Newcome

Try adjusting your logic so that

Timeline End >= Resource Start

and

Timeline Start <= Resource End

This should grab any overlap.

Paul Newcome

Try adjusting your logic so that

Timeline End >= Resource Start

and

Timeline Start <= Resource End

This should grab any overlap.

Gina Minard-Rivera

Thanks@Paul Newcome that did the trick! So simple I suppose, but totally not on my radar. Appreciate your help.

Paul Newcome

Happy to help. 👍️