Resource allocation based on week numbers

[maximelling]

Hi,
I am trying to have excel count the week numbers automatically based on the duration of the project.
I have attached an example.

The red text is filled in manually.
The blue text is based on the week number function based on the date I put in.
What I want to achieve is when I add a number to "Weeks in total" I want that number, i.e. 2, to automatically give me the week numbers for the duration of the job.
If it is 4 weeks, I want it to count all 4 week numbers per column. Columns highlighted in green.

The purpose of it is to be able to use a table in another sheet counting the value in the week numbers column to show it on a timeline.
So if Resource 1 (Name 1) has a job starting 01.january 2023 and it lasts for 2 weeks I need excel to automatically find both week numbers and show me in the table.
The desired output is marked in blue.

I really hope that this is doable

[Glenn Kennedy]

Delete the expected results.

=SEQUENCE(,C2,WEEKNUM(B2,2))

copied down.

[maximelling]

Hi, thank you! This is extactly what I needed.

And this has caused another question, while trying to extract the values I have a timeline like this:

Attachment 811697

The formula to get the data:
=COUNTIFS(PROJECTS!B:B;"Active";PROJECTS!M:M;C9;PROJECTS!P:P;2)
Where 2 is the week date. My problem now is that I do not only get week numbers from column P, but all the way to W. How can I write the COUNTIFS with multiple columns?

Thanks

[Glenn Kennedy]

A picture is of little use. Post an Excel sheet, please.

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

1 is the week number.

Note: Try to avoid referring the whole column or whole row. It will slow down Excel. Give some definite range, say B1:B10000.

[maximelling]

Hi,
I tried adding the SUMPRODUCT but it did not work.
I have uploaded an example.

[kvsrinivasamurthy]

What does W1 ,W2 indicate.
Pl see file. Formula working

[Glenn Kennedy]

Is this it?

=SUMPRODUCT(($B$2:$B$5=$P10)*($E$2:$J$5=Q$9))

[maximelling]

Yes, it works now!

THANK YOU!!!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[maximelling]

Hi,
sorry - I am missing the function to ensure that only active projects are a part of the equation.
The status Active is in column A.


I tried kvsrinivasamurthy's solution, but I did not get it to work.

[HansDouwe]

One way: Expands on Glenn's formula in Post #8:

Please Login or Register  to view this content.

[maximelling]

That worked! Thank you!!

[HansDouwe]

Glad to have helped. Thanks for the feedback and rep .