minus using pivot table

  • Thread starter Thread starter ronsrst
  • Start date Start date
R

ronsrst

Hi!,

Can someone help me with minusing data using pivot table or any othe
way, for example:

Emp id Time
111 16:00
111 15:42
111 14:22
111 12:00
222 15:30
222 12:10
222 10:00

Data I have is emoloyee ID and various time that they performe
something in system. In the result I want to get Employee ID and tota
time they were in the system.

This means final result should be
Emp id Time
111 4:00
222 5:30

It just should substract the lowest time from the highest time.

Thanks in advance..
Ro
 
One way ..

Assuming this table is in Sheet1, A1:B8
Emp id Time
111 16:00
111 15:42
111 14:22
111 12:00
222 15:30
222 12:10
222 10:00
Click to expand...

In say, Sheet2
-------------
With the table below in A1:B3

Emp id Time
111
222

Select B2

Put in the *formula bar*:

=MAX(IF(Sheet1!$A$2:$A$8=A2,Sheet1!$B$2:$B$8))-MIN(IF(Sheet1!$A$2:$A$8=A2,Sh
eet1!$B$2:$B$8))

Array-enter (i.e. Press CTRL+SHIFT+ENTER)

Done correctly, Excel will wrap curly braces { } around the formula

Format B2 as time viz.:

Click Format > Cells > Custom > Type: [h]:mm
(With the square brackets around the "h")

Copy B2 down to B3

B2 and B3 will return 4:00 and 5:30
 
Back
Top