Total time and Effective Time

[Ashhu]

Hi All
Need help to come up with solution to find Employee time spent based on entry and exit details. Entry and exit time comes from swipe machine. See attached sheet, i have manually put formula for one employee to show the result.

Info:

1. There are 100+ employees.
2. Each day there will 2000+ entry and exits.
3. Emp Code should drive the result.

 

[rahulshewale1]

Hii,

please find the attached sheet.

Formula in J4-
LOOKUP(I4,$F$4:$F$19,$G$4:$G$19)-VLOOKUP(I4,$F$4:$G$19,2,FALSE)

Formula in K4--
SUMPRODUCT(($F$4:$F$19=I4)*($D$4:$D$19="EXIT")*$G$4:$G$19)-SUMPRODUCT(($F$4:$F$19=I4)*($D$4:$D$19="ENTRY")*$G$4:$G$19)

Thanks
rahul shewale

 

[Ashhu]

Hii,

please find the attached sheet.

Formula in J4-
LOOKUP(I4,$F$4:$F$19,$G$4:$G$19)-VLOOKUP(I4,$F$4:$G$19,2,FALSE)

Formula in K4--
SUMPRODUCT(($F$4:$F$19=I4)*($D$4:$D$19="EXIT")*$G$4:$G$19)-SUMPRODUCT(($F$4:$F$19=I4)*($D$4:$D$19="ENTRY")*$G$4:$G$19)

Thanks
rahul shewale

Thanks Rahul Ji

The requirement fulfilled half.

Above formula works well when we have one employee but when the number of employees are more and need to sort based on date and also emp code then it will be issue.
But i am able to fix one which is in K4
Effective Time (K4) i am able to fix it by multiplying another array but the one which calculates using Lookup and Vlookloop need fix.

Please see attached.

 

[bosco_yip]

To calculate TT and ET by giving Emp. Number and Date.

1] "Total Time" K4, formula copy down :

=SUM(VLOOKUP(1,IF({1,0},INDEX(($B$4:$B$1000=I4)/($F$4:$F$1000=J4),0),$G$4:$G$1000),2,N(IF(1,{0;1})))*{-1;1})

2] "Effective Time" L4, formula copy down :

=SUMPRODUCT((B$4:B$1000=I4)*(F$4:F$1000=J4)*((D$4:D$1000={"EXIT","ENTRY"})*{1,-1})*G$4:G$1000)

Regards
Bosco

 

[Ashhu]

View attachment 43400

To calculate TT and ET by giving Emp. Number and Date.

1] "Total Time" K4, formula copy down :

=SUM(VLOOKUP(1,IF({1,0},INDEX(($B$4:$B$1000=I4)/($F$4:$F$1000=J4),0),$G$4:$G$1000),2,N(IF(1,{0;1})))*{-1;1})

2] "Effective Time" L4, formula copy down :

=SUMPRODUCT((B$4:B$1000=I4)*(F$4:F$1000=J4)*((D$4:D$1000={"EXIT","ENTRY"})*{1,-1})*G$4:G$1000)

Regards
Bosco

wats going on here.....
great one Bosco...i appreciate....
what a use of Sumproduct......absolute respect.

thanks bro