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

Archemoros

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)

Paul Newcome

I think you have the logic backwards. Once you flipped the arguments as I suggested, you are now looking for rows where the travel start is before [Date] and the travel end is after [Date]. Previously you were trying to count rows where the travel end was before [Date] and travel start was after [Date] which is impossible.

Paul Newcome

Are you able to provide a screenshot (sample data is fine) for context?

Archemoros

This is the sheet for which I'm trying to write the equation (in each one of the cells with a zero in it.

Archemoros

This is the source data with resources and travel dates.

Paul Newcome

Try flipping your date arguments. Right now you are wanting to count rows where the start date is in the future and the end date is in the past.

Archemoros

Same results — all returned zero. When I remove one of the conditions, it counts all the days a resource is on a trip, for the year. Feels like if I could properly bound the third condition, I wouldn't exclude given dates from the count, for my resources.

Paul Newcome

Can you paste your newest formula here?

Archemoros

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

Paul Newcome

You still haven't flipped the arguments. End date should be greater than or equal to and Start date should be less than or equal to.

Archemoros

It worked Paul, but I'm confused about why. If I want the rows that a certain resource is traveling, shouldn't the rows I want, be when at or after the first day of travel, and at or before the last day of travel? By making the equation look for rows that are less than or equal to the first day of travel and greater than or equal to the last day of travel, shouldn't it be looking then, for all days NOT being travelled?

Like I said, it works now. Am I misunderstanding the syntax of the @cell and @row operators?

Paul Newcome

I think you have the logic backwards. Once you flipped the arguments as I suggested, you are now looking for rows where the travel start is before [Date] and the travel end is after [Date]. Previously you were trying to count rows where the travel end was before [Date] and travel start was after [Date] which is impossible.

Archemoros

Gotcha. Upon re-review, it makes perfect sense. Thanks, again.