Summing entries belong to weeks of each month

Help Studio
1

Hello. I have an excel sheet like below. Here, columns show the week number of year and rows show projects that done by a person on that week. I want to sum this projects for each month. For instance, program will find which weeks belong to january (1-2-3-4) then sum the project count for each person and write it to the total column. I couldn’t figure how can I find monthly sum. Do you have any idea about this?

image

1 Like
2

Hi @Anna.Dewitt ,

Just to clarify you mentioned two things
1. You want sum on monthly basis like for week 1 would be 14 what is visible here.
2. You want sum on Monthly weeks basis like in January has weeks 1,2,3,4 then sum should be what is there in those 4 columns.

Out of the two approaches what do you want, can you please explain a bit.

Thanks !!

3

Sorry for bad explanation, I want to do second task that you write: “You want sum on Monthly weeks basis like in January has weeks 1,2,3,4 then sum should be what is there in those 4 columns.”

For instance, for second person january will be 6, february will be 3, march will be 0.

4

A main building block would be to calculate which month is mapped to which calendar week

we can do it generic - 4 week = 1 month or more specific for a year

grafik
grafik721×165 31 KB

Once we calculated the Month/WeekNo Sets then we can do the summing up.

We would recommend to clear this detail also

5

In your code, first code calculates week count in 2022, second calculates first week of 2022 from my understanding. What does third code do? I didn’t understand that. Why is it used for?

6

Both first two Lines are calculating the week number for the first January 2022
There are some cultural / location-based differences e.g. when does a week start (Sunday or Monday). Therefore we do have some more additional parameters to control it. This was showcased to you.

In line 3 we used the same parameter which gave us weekno 1 for first JAN 2022 and got WeekNo 6 for the first Feb 2022. Here we showcased that a conservative approach of 1 Month = 4 Weeks will risk being faulty.