Hi Roger,

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


"Roger Govier" wrote:

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, FormatCellsNumberCustom [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

"Praveen Potturi" wrote in
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.