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
-
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.
Answers
-
Are you able to provide a screenshot (sample data is fine) for context?
-
This is the sheet for which I'm trying to write the equation (in each one of the cells with a zero in it.
-
This is the source data with resources and travel dates.
-
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.
-
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.
-
Can you paste your newest formula here?
-
=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)
-
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.
-
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?
-
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.
-
Gotcha. Upon re-review, it makes perfect sense. Thanks, again.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!