Running Total for Time Spent for a project in 1 month

[Guest]

I am working on a sheet that is to track meeting times.

I would like to be able to have someone put the date of the meeting, the
start time, end time. Excel will calculate the length. And then there will be
seperate columns for each employee. And there is an option for the user to
type in that the employee is not at the meeting.

I would like to have Excel calulate monthly totals for each employee for
each meeting they were at in that month.

(Example: Bill was at the Jan 5, Jan 12 meetings that were both 60 minutes,
but he was not at the Jan 19 meeting that was also 60. But Jill has been to
all 3)

I would like to have a spot that will say 120 for January meeting minutes
for Bill, and 180 for Jill.

If someone needs me to post a rough outline of my chart I can. I posted this
question before, but having problems moving it from the help I got to my
sheet.

 

[Guest]

Try setting up your spreadsheet like this


A B C D
1 2/18 2/25 3/1

2 3 hours 2 hours 4 hours

3 Tom Y N Y

4 3 0 4

5 Dick Y Y N

6 3 2 0

7 Harry N Y N

8 0 2 4

 

[Guest]

How would I have it to sum up the amount of time in Feb for meetings that tom
Attended?

 

[vezerid]

What would I do:
I would have a table of meetings. Meeting ID (e.g. 1, 2, 3), Date,
Duration. Say I have these in a spreadsheet called Meetings in columns
A:C.
I would have a table of attendances. Just two columns: Meeting ID,
Person. Say I have these in a spreadsheet called Attendances in
columns A:B.
Meeting Person
1 Bill
1 Jill
2 Bill
2 Jill
3 Jill

In Attendances!C2 I can bring the Date with VLOOKUP:
=VLOOKUP(A2,Meetings!A:C,2,FALSE)

In Attendances!D2 I can bring the Duration with VLOOKUP:
=VLOOKUP(A2,Meetings!A:C,3,FALSE)

Now, to get Bill's total minutes during, say, January 2007 (and
assuming your Attendances table has data in A2:B20:

=SUMPRODUCT(D220*(TEXT(C2:C20,"Mmmm YYYY")="January
2007")*(B2:B20="Bill"))

HTH
Kostis Vezerides

 

[Roger Govier]

Hi

Assuming from your description that your data is set out as follows
A1 Date
B1 Start time
C1 End Time
D1 Duration (minutes)
E1 Emp1
F1 Emp2 etc.

Also, assuming that you enter an "x" in the column under the employee's
name to signify attendance at that meeting

On sheet2 set up a table with
A1 Date
B1 Emp1
C1 Emp2 etc
with A2 holding 01/Jan/07, A3 holding 01/Feb/07 etc. then in B2 enter

=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$20)=MONTH(Sheet2!$A2))*
(Sheet1!E$2:E$20="x")*Sheet1!$D$2:$D$20)

Copy across through cells C2 as far as the employee list extends
Copy down for the 12 months of the year.

Extend the ranges from 20 as in above formula to cover the number of
rows of data that you are likely to have on Sheet1

--
Regards

Roger Govier


"Chart_Maker_Wonderer" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

That worked great, and was able to tweak the code so I could place both
tables on the same worksheet.

Thanks.