Count employee (resource) tasks from second smartsheet if the tasks fall in a date range
I am trying to count the how many tasks a resource is assigned in a project schedule (sheet 1) if they are in a date range (sheet 1). The calculations is being done in second sheet (sheet 2).
Example: Peter (resource field is sheet 1) has 10 tasks in a Planned Start date ( Planned start date field in sheet 1). I'd like to see the tasks from 3/18/24 through 3/23/24.
I tried the following formulas.
Attempt 1:
=COUNTIFS({Assign To}, Teammates@row, True([Planned Start Date]@row >= DATE(2024, 3, 18), [Planned Finished Date]@row <= DATE(2024, 3, 23))) - Error unparsehable.
Attempt 2:
I simply and add two fields to the current sheet to eliminate using two sheets until I figure this out.
=COUNTIFS({Assign To}, Teammates@row, ([Planned Start Date]@row “>=DATE(2024, 3, 18)” AND “<= DATE(2024, 3, 23)”) = Error unparsehable.
Attempt:
=COUNTIFS({Assign To}, Teammates@row, AND([Planned Start Date]@row >= DATE(2024, 3, 18), [Planned Finished Date]@row <= DATE(2024, 3, 23))) - Invalid Operator
I know the count for teammates work. I just can't figure out the date range piece.
Any help would be appreciated!
Thank you.
Best Answer
-
Are you getting an error message when you use the below?
=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Start Date}, <= DATE(2024, 3, 23))
Answers
-
You would use something more along the lines of:
=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Finish Date}, <= DATE(2024, 3, 23))
-
Paul
Thank you for answer.
To give more information. The goal is to show the amount of tasks that started by using "Planned Start date" in a week. There will be a second field, with a separate formula, showing "Actual start date."
So using only "planned start date", show me the count for employee Bob tasks that started for the week of 3/18/24. Then I have a second field that is counting Bob tasks that were actual started the week of 3/18/24.
These two formulas will be done for each week of a given month.
Right now, I am working on the planned date calculation figuring I can used the same formula for Actual start date.
Is there a way to combine a date range for the planned start date field, or do I have to list the date field twice?
Below formula, I tried to repeat the Start Date, but Smartsheet didn't like this.
=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Start Date}, <= DATE(2024, 3, 23))
-
I tried this as well, but Smartsheet doesn't seem to like the "and" operator. Error = Invalid Operator
=IF([Planned Start Date]@row >= DATE(2024, 3, 18), IF(AND([Planned Start Date]@row <= DATE(2024, 3, 23), COUNTIFS({ Assign To}, Teammates@row))))
-
Are you getting an error message when you use the below?
=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Start Date}, <= DATE(2024, 3, 23))
-
Paul
Thank you so much!
-
Is it working now? All I did was copy/paste one of your formulas that you wrote based on the syntax I had provided.
-
I missed a comma in my formula. Reading your formula, helped me step back, and made it very clear.
Final formula I used:
=COUNTIFS({Assign To}, Teammates@row, {Planned start 3 11-15 2024}, >=DATE(2024, 3, 18), {Planned start 3 11-15 2024}, <=DATE(2024, 3, 23))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!