Help with working out overlap on vacation and project tasks

Hello, I need some help with a formula I'm trying to modify from here

Formula to Link PTO Tracker to Project Schedule — Smartsheet Community

I keep getting #INVALID REF

I'm new to Smartsheet so a lot of this is going over my head, my workflow is finding something close to what I need and feeding it through GPT with prompts until it spits out the right result, learning as I go.

In this case it is the first time I'm trying to ref something from another sheet so I'm a bit lost.

My ref sheet is "Planned Leave" with "Assignee", "First DOL" and "Last DOL" Columns.

My "Project sheet" where the formula lives has "Assigned To", "Start Date" and "End Date"

I've got this so far

=IF(AND(COUNTIFS({Planned Leave Assignee}, [Assigned To]@row, {Planned Leave First DOL}, @cell <= [End Date]@row, {Planned Leave Last DOL}, @cell >= [Start Date]@row) > 0, @cell <= [End Date]@row, @cell >= [Start Date]@row), [End Date]@row - [Start Date]@row + 1, 0)

Any help would be appreciated.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!