how to calculate man hours for different project ?

  • Thread starter Thread starter sg_s123
  • Start date Start date
S

sg_s123

============================================================================
02-Feb-04 03-Feb-04
Staff Staff 0800hr- 1300hr- 1700hr- 1900hr- 0800hr- 1300hr- 1700hr-
1900hr-
Number Name 1200hr 1700hr 1900hr 2200hr 1200hr 1700hr 1900hr
2200hr
============================================================================
4654 Saniff A A A C A A A C
4437 Joey B C B C B C B C
4479 Elmer C C B C C C B C
4484 Garry C C A B C C A B
4509 Philip A C A B A C A B
4903 Siti A C A B A C A B


=> The above monthly data is from excel (staffproject.xls). It comes
with staff number, staff name and 1 month timeslot data (divided into
4 time slots for each day). Each time slot is equivalent to the 4
hours, 4 hours, 2 hours, 3 hours respectively. "A,B,C,D" represent
project code. My question is :

1. How do I calculate for Project A,B,C,d, how many man hours are used
for different project?

I would appreciate if any body could share some light on this.
 
A B C D E F G H I J
1 02-Feb-04 03-Feb-04
2 Staf Staf 08:00 13:00 17:00 19:00 08:00 13:00 17:00 19:00
3 Nmbr Name 12:00 17:00 19:00 22:00 12:00 17:00 19:00 22:00
4 ==== ===== ===== ===== ===== ===== ===== ===== ===== =====
5 4654 Sanif A A A C A A A C
6 4437 Joey B C B C B C B C
7 4479 Elmer C C B C C C B C
8 4484 Garry C C A B C C A B
9 4509 Phil A C A B A C A B
10 4903 Siti A C A B A C A B
11 ==== ===== ===== ===== ===== ===== ===== ===== ===== =====
12 A 48 12 4 8 0 12 4 8 0
13 B 34 4 0 4 9 4 0 4 9
14 C 74 8 20 0 9 8 20 0 9
15 D 0 0 0 0 0 0 0 0 0

1. Enter Formula =SUMPRODUCT((C$5:C$10=$A12)*(C$3-C$2)/"1:0")
into Cell C12 and copy it downward to Cell C15.
2. Copy Range C12:C15 rightward as long as you want.
3. Enter Formula =SUM(C12:DZ12) and copy it downward.
 
Back
Top