Sum of weekly data

[Praveen Potturi]

Hi,

Can anyone help in doing sum of daily data with respect to weekdays. I want
to get the sum of everydays data as week1, week2..etc. for that month

Example:-


A B C D E
1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul
2 Break 0:00:00 0:00:00 0:00:00 0:00:00
3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00
4 Training 0:00:00 0:00:00 0:00:00 0:00:00
5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00
6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00
7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00
8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00
9 Total 0:00:00 0:00:00 0:00:00 0:00:00
10
11
12
13 Week1 Week2 Week3 Week4
14 Break
15 Meeting
16 Training

In the above example I want a formula in B14, C14, D14, E14 which can do sum
of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't
have to map it everytime manually. I want excel to recongnize the data
according to the dates in B1, C1, D1.

So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of
B2+C2+D2 and result should show in B14.

 

[Stefi]

What happens if a month consists of more than four weeks? E.g.
1st week of July 2008 is 1-July to 6-July
2nd week of July 2008 is 7-July to 13-July
....
5th week of July 2008 is 28-July to 31-July

Regards,
Stefi

„Praveen Potturi†ezt írta:

 

[Bob Phillips]

How do you define a week? Is Week1 the first seven days in a month, the
days up to the first Sat/Sun? Or some other rule?

 

[Praveen Potturi]

Hi Stefi,

I need data of all weeks whether its 4 weeks or 5 weeks. Awaiting for
response.

 

[Praveen Potturi]

hi Bob,

Week means only weekdays i.e Monday to Friday. Sat and Sun shouldnot be
considered.

 

[Roger Govier]

Hi

I would insert a new row above your data and in B1 enter
=WEEKNUM(B2)
Copy across through C1:AF1

In your "new" row 14, in A14 enter Week
in B14:F14 enter 27,28,29,30,31
In B15 enter
=SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2,2)<6)*$B3:$AF3)
Format cell B15, Format>Cells>Number>Custom> [hh]:mm
and copy across through C15:F15

This will give your the total of hours for that week number for weekdays
only for hours spent in Break

Copy B15:F15 down through B16:B20 to give the totals for the other Task
types.

Each month you will need to change the values in cells B14:F14 to reflect
the week numbers belonging to that particular month. Sometimes there will be
4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week
month.

 

[Bob Phillips]

Yes, but for instance, is Week 1 in this month July, the 1st to the 7th, or
1st to 4th because the 7th day is Monday and is therefore week2.

You must to have VERY explicit rules.

 

[Praveen Potturi]

Week1 for July should be from 1st to 4th, week2 should be from 7th to 11th,
week3 should be from 14th to 18th and so on and week5 will be 28th to 31st.

 

[Praveen Potturi]

Hi Roger,

Weeknum function is not working for me. Plz help
--
Thanks,
Praveen Potturi

Hi

I would insert a new row above your data and in B1 enter
=WEEKNUM(B2)
Copy across through C1:AF1

In your "new" row 14, in A14 enter Week
in B14:F14 enter 27,28,29,30,31
In B15 enter
=SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2,2)<6)*$B3:$AF3)
Format cell B15, Format>Cells>Number>Custom> [hh]:mm
and copy across through C15:F15

This will give your the total of hours for that week number for weekdays
only for hours spent in Break

Copy B15:F15 down through B16:B20 to give the totals for the other Task
types.

Each month you will need to change the values in cells B14:F14 to reflect
the week numbers belonging to that particular month. Sometimes there will be
4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week
month.

--
Regards
Roger Govier

Hi,

Can anyone help in doing sum of daily data with respect to weekdays. I
want
to get the sum of everydays data as week1, week2..etc. for that month

Example:-


A B C D E
1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul
2 Break 0:00:00 0:00:00 0:00:00 0:00:00
3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00
4 Training 0:00:00 0:00:00 0:00:00 0:00:00
5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00
6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00
7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00
8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00
9 Total 0:00:00 0:00:00 0:00:00 0:00:00
10
11
12
13 Week1 Week2 Week3 Week4
14 Break
15 Meeting
16 Training

In the above example I want a formula in B14, C14, D14, E14 which can do
sum
of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't
have to map it everytime manually. I want excel to recongnize the data
according to the dates in B1, C1, D1.

So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of
B2+C2+D2 and result should show in B14.

 

[Bob Phillips]

=SUMPRODUCT((MONTH($B$1:$AF$1)=7)*(WEEKDAY($B$1:$AF$1,2)<6)*
((DATE(2008,7,-DAY($B$1))+(COLUMN()-1)*7)>=$B$1:$AF$1)*
($B$1:$AF$1<DATE(2008,7,-DAY($B$1))+(COLUMN()-1)*7)*
($B$2:$AF$8))-
N(SUM($A12:A12))

 

[Dave Peterson]

Did you use Tools|Addins and make sure that the analysis toolpak was checked.

Hi Roger,

Weeknum function is not working for me. Plz help
--
Thanks,
Praveen Potturi

Hi

I would insert a new row above your data and in B1 enter
=WEEKNUM(B2)
Copy across through C1:AF1

In your "new" row 14, in A14 enter Week
in B14:F14 enter 27,28,29,30,31
In B15 enter
=SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2,2)<6)*$B3:$AF3)
Format cell B15, Format>Cells>Number>Custom> [hh]:mm
and copy across through C15:F15

This will give your the total of hours for that week number for weekdays
only for hours spent in Break

Copy B15:F15 down through B16:B20 to give the totals for the other Task
types.

Each month you will need to change the values in cells B14:F14 to reflect
the week numbers belonging to that particular month. Sometimes there will be
4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week
month.

--
Regards
Roger Govier

Hi,

Can anyone help in doing sum of daily data with respect to weekdays. I
want
to get the sum of everydays data as week1, week2..etc. for that month

Example:-


A B C D E
1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul
2 Break 0:00:00 0:00:00 0:00:00 0:00:00
3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00
4 Training 0:00:00 0:00:00 0:00:00 0:00:00
5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00
6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00
7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00
8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00
9 Total 0:00:00 0:00:00 0:00:00 0:00:00
10
11
12
13 Week1 Week2 Week3 Week4
14 Break
15 Meeting
16 Training

In the above example I want a formula in B14, C14, D14, E14 which can do
sum
of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i don't
have to map it everytime manually. I want excel to recongnize the data
according to the dates in B1, C1, D1.

So if dates in B1, C1 D1 are weekdays of 1st week then it should sum of
B2+C2+D2 and result should show in B14.

 

[Roger Govier]

Thanks Dave.
Totally forgot to mention that

--
Regards
Roger Govier

Did you use Tools|Addins and make sure that the analysis toolpak was
checked.

Hi Roger,

Weeknum function is not working for me. Plz help
--
Thanks,
Praveen Potturi

Hi

I would insert a new row above your data and in B1 enter
=WEEKNUM(B2)
Copy across through C1:AF1

In your "new" row 14, in A14 enter Week
in B14:F14 enter 27,28,29,30,31
In B15 enter
=SUMPRODUCT(($B$1:$AF$1=B$14)*(WEEKDAY($B$2:$AF$2,2)<6)*$B3:$AF3)
Format cell B15, Format>Cells>Number>Custom> [hh]:mm
and copy across through C15:F15

This will give your the total of hours for that week number for
weekdays
only for hours spent in Break

Copy B15:F15 down through B16:B20 to give the totals for the other Task
types.

Each month you will need to change the values in cells B14:F14 to
reflect
the week numbers belonging to that particular month. Sometimes there
will be
4 weeks and sometimes 5. Just leave cell F14 blank when it is a 4 week
month.

--
Regards
Roger Govier

message Hi,

Can anyone help in doing sum of daily data with respect to weekdays.
I
want
to get the sum of everydays data as week1, week2..etc. for that month

Example:-


A B C D E
1 Task Type 1-Jul 2-Jul 3-Jul 4-Jul
2 Break 0:00:00 0:00:00 0:00:00 0:00:00
3 Meeting 0:00:00 0:00:00 0:00:00 0:00:00
4 Training 0:00:00 0:00:00 0:00:00 0:00:00
5 Mentoring 0:00:00 0:00:00 0:00:00 0:00:00
6 Celebrations 0:00:00 0:00:00 0:00:00 0:00:00
7 System Dow 0:00:00 0:00:00 0:00:00 0:00:00
8 Surplus 0:00:00 0:00:00 0:00:00 0:00:00
9 Total 0:00:00 0:00:00 0:00:00 0:00:00
10
11
12
13 Week1 Week2 Week3 Week4
14 Break
15 Meeting
16 Training

In the above example I want a formula in B14, C14, D14, E14 which can
do
sum
of weekdays (B2+C2+D2+E2) according to week1, week2 week3 so that i
don't
have to map it everytime manually. I want excel to recongnize the
data
according to the dates in B1, C1, D1.

So if dates in B1, C1 D1 are weekdays of 1st week then it should sum
of
B2+C2+D2 and result should show in B14.