sum if
 

04/23/05 1331 CLOCK IN 10:00
04/23/05 5070 CLOCK IN 11:00
04/23/05 1976 CLOCK IN 12:00
04/23/05 1976 CLOCK OUT 19:00
04/23/05 1331 CLOCK OUT 19:00
04/23/05 5070 CLOCK OUT 20:00

this table is added to every day,
basically i need to find out how many hours each employee worked each day
im thinking i need a triple sum if? if this date, this employee, if clock out
and subtract that from if this date, this employee, if clock in.

thanks in advance

sum if
 

A triple sort might also work:

first by date
second by employee number
last by time
Now the data is in pairs of clock-ins and clock-outs.

Then in cell E2 a formula like =IF(B2=B1,D2-D1,"") and copy the cell down
the column
--
Gary's Student


"choice" wrote:

04/23/05 1331 CLOCK IN 10:00
04/23/05 5070 CLOCK IN 11:00
04/23/05 1976 CLOCK IN 12:00
04/23/05 1976 CLOCK OUT 19:00
04/23/05 1331 CLOCK OUT 19:00
04/23/05 5070 CLOCK OUT 20:00

this table is added to every day,
basically i need to find out how many hours each employee worked each day
im thinking i need a triple sum if? if this date, this employee, if clock out
and subtract that from if this date, this employee, if clock in.

thanks in advance

sum if
 

=(SUMPRODUCT(--(B1:B100=1976),(--(D1:D100="OUT")),E1:E100)-SUMPRODUCT(--(B1:
B100=1976),(--(D1:D100="IN")),E1:E100))*24

--
HTH

Bob Phillips

"choice" wrote in message
...
04/23/05 1331 CLOCK IN 10:00
04/23/05 5070 CLOCK IN 11:00
04/23/05 1976 CLOCK IN 12:00
04/23/05 1976 CLOCK OUT 19:00
04/23/05 1331 CLOCK OUT 19:00
04/23/05 5070 CLOCK OUT 20:00

this table is added to every day,
basically i need to find out how many hours each employee worked each day
im thinking i need a triple sum if? if this date, this employee, if clock
out
and subtract that from if this date, this employee, if clock in.

thanks in advance

sum if
 

"choice" ha scritto nel messaggio


04/23/05 1331 CLOCK IN 10:00
04/23/05 5070 CLOCK IN 11:00
04/23/05 1976 CLOCK IN 12:00
04/23/05 1976 CLOCK OUT 19:00
04/23/05 1331 CLOCK OUT 19:00
04/23/05 5070 CLOCK OUT 20:00

this table is added to every day,
basically i need to find out how many hours each employee worked each
day
im thinking i need a triple sum if? if this date, this employee, if
clock out and subtract that from if this date, this employee, if
clock in.


Maybe, you could reorganize your data table in this way:

A B C D
E
1 date ID# CLOCK IN CLOCK OUT WORKED HOURS
2 04/23/05 1331 10:00 19:00 =D2-C2
3 04/23/05 1976 12:00 19:00 =D3-C3


--
Hoping to be helpful...

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------