calculate time based on criteria

[losdamianos]

Hello


I have a spreadsheet with task list

A B C D E F

name task desc time date time spent


Each person scan his card (name, task ,desc ) time and date is calculated automaticly.

Im trying to calculate time spent on each task So when Person A appear again, in previous record for Person a time spent will be calculated ( new record column E - previous record column E )

column f has to find NEXT record with that name and calculate time difference


please see attached

http://dl.dropbox.com/u/8597042/example.xls

 

[Hui]

Losdamianos


in Excel 2007/10

In F2 use: =IFERROR(INDEX(D3:$D$7,MATCH(A2,A3:$A$7,0))-D2,"")


in Excel 97/03

In F2 use: =IF(ISERROR(INDEX(D3:$D$7,MATCH(A2,A3:$A$7,0))-D2),"",INDEX(D3:$D$7,MATCH(A2,A3:$A$7,0))-D2)


Then format the cell as [h]:mm:ss

Change the $7 to match the end of your data area


Copy down

 

[losdamianos]

Thanks, I've managed to do this using 2nd match from vlookup and helper column so I ended up with PersonA1

PersonB1

PersonA2


etc

then simply vlookup formula + counta (u get the idea )


anyway your formula is much cleaner

thanks !