Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum if
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|