Excel - INDEX, MATCH, SUMIFS?! TIME & ATTENDANCE REPORT

**ccernat** · 07-09-2012, 04:44 AM

Hello,

I have a little problem wich I'm not able to solve it on my own. So, I have a table with employees going IN and OUT at various times. I want to log somehow their weekly efficiency by actually measuring how much do they work daily, and then a sum by week number. But first I need the amount of hours worked daily, so the difference between the first IN for that specific day, and the last OUT, all knowing that the total amount of break time they have is 20 mins. Then I want the total by employee name and week number for the hours that this guy actually worked. Please see attached to understand better what i'm describing. It will be a HUGE help for me to find a solution.

Tks ALOT!!

**dilipandey** · 07-09-2012, 04:54 AM

Hi ccernat,

I am not able to understand "Real Teoretic" in the upper table and which columns in below table shows the In Time and Out Time ?

Regards,
DILIPandey

<click on below 'star' if this helps>

**ccernat** · 07-09-2012, 10:15 AM

Thanks alot for you quick repy!

The column with In and Exit is called: fReaderName. And Real = the sum of all the hours worked by that specific worker in that specific week. Teoretic is what he is supposed to work: 5 days per week x 8 hours per day.

**dilipandey** · 07-09-2012, 12:54 PM

Okay..

aldea mihai got in on 5/18/2012 03:07:20 PM but exited thrice on 5/18/2012, but if consider the maximum exit time i.e, 05/18/2012 03:07:54 PM and subtract this with In time the result would be 34 seconds... am I on right track ?

Regards,
DILIPandey

<click on below 'star' if this helps>

**ccernat** · 07-10-2012, 03:55 AM

Yes. I neet the time difference between the Last EXIT - First IN, for that specific worker, in that specific day, and then add all the days worked in that specific week and output the result into that big table with week numbers, in the Real column.

**dilipandey** · 07-10-2012, 10:47 AM

HI ccernat,

I have used SumIF function plus helper column(s) to achieve the desired result, see the attachment and let me know if this helps. Thanks.
ATTENDANCE_SAMPLE.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**ccernat** · 07-11-2012, 08:18 AM

wonderful, its working great, except that its only for one worker. if in my list appear various other workers wich also they worked in those specific days, the formula adds all the hours for all workers, and not for each one in particular. can so

**dilipandey** · 07-11-2012, 08:31 AM

Ok.. to handle that you can use SumIFs function as outlined below :-

=SUMIFS(Table1[Column4],Table1[f_ConsumerName],$A3,Table1[WeekNO],B$1)

Regards,
DILIPandey

<click on below 'star' if this helps>

**ccernat** · 07-11-2012, 09:31 AM

No, i want in the column where you calculated the difference between MAX and MIN, but you only conditioned by date, and i need also by name of the employee. I tried smth like:

=MAX(IF(AND($J$2:$J$660=$J2;$B$2:$B$660=$B2);$G$2:$G$660;""))-MIN(IF(AND($J$2:$J$660=$J2;$B$2:$B$660=$B2);$G$2:$G$660;""))

But it didnt work, it returned #VALUE. I tried to put 2 condition for the min and max: the date to be the same and the employee name to be the same in the same time with the AND operator. Can you see whats wrong?
THKS ALOT!

**dilipandey** · 07-11-2012, 09:37 AM

Ok.. no worries...

You can revise the formula in I21 cell in similar fashion :-

{=MAX(IF(($B$21:$B$66=$B21)*($J$21:$J$66=$J21),$G$21:$G$66,""))-MIN(IF(($B$21:$B$66=$B21)*($J$21:$J$66=$J21),$G$21:$G$66,""))}

Regards,
DILIPandey

<click on below 'star' if this helps>

**ccernat** · 07-11-2012, 09:52 AM

GREAT!!! Tks alot!!

**dilipandey** · 07-11-2012, 09:54 AM

You are welcome ccernat

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

**sasa.bgd** · 08-20-2013, 04:57 AM

Hi,
could you help me , i have similar problem to this one,difference is only i need to calculate how many time user spent IN ..in this formula it is calculated last exit and first in.
I have a few IN's and OUT every day and somehow need to know time spent Inside the office.
Here is my output from access control.

Thanks