How do I see if a resource has (or is) working a specific date?


I have resource names (one per column on the destination sheet, and one per row in the source sheet), start dates for jobs (one per row in the source data sheet), and end dates (same) for the same jobs. I'm trying to use an equation to see if the resource is working every date, throughout the year (one date per row, in the destination sheet). This will allow me to see their team's bandwidth vs capacity, over the course of whatever date range I choose, on a day-to-day basis. I keep getting zero returned for every date this equation is checking, even though I can go to the source data and see they're working several dates in the ranges. If I remove the End Date condition, I get a count from that row's date to the end of the range. When I re-add the End Date condition though, it all goes to zero. HELP! The equation is:

=COUNTIFS({Resource Source Sheet Column}, [Resource Name in Destination Sheet]$1, {Start Date Column in Source Sheet}, @cell >= $Date@row, {End Date Column in Source Sheet}, @cell <= $Date@row)


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!