Count employee (resource) tasks from second smartsheet if the tasks fall in a date range

Mar.Max

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.

Paul Newcome

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 Newcome

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))

Mar.Max

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))

Mar.Max

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))))

Paul Newcome

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))

Mar.Max

Paul

Thank you so much!

Paul Newcome

Is it working now? All I did was copy/paste one of your formulas that you wrote based on the syntax I had provided.

Mar.Max

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))