Using excel to manage worked houres !?

Use these array formula for in and out

=INDEX(MOD('Data exported from acces sistem'!$C$2:$C$20,1),MATCH(1,('Data exported from acces sistem'!$A$2:$A$20=$A3)*(INT('Data exported from acces sistem'!$C$2:$C$20)=$B$1),0))

=INDEX(MOD('Data exported from acces sistem'!$I$2:$I$20,1),MATCH(1,('Data exported from acces sistem'!$G$2:$G$20=$A3)*(INT('Data exported from acces sistem'!$I$2:$I$20)=$B$1),0))

You will need to put proper dates in B1, E1, etc., and adjust the $B$1 to the appropriate date field for each day results.

Originally Posted by Bob Phillips

Use these array formula for in and out

=INDEX(MOD('Data exported from acces sistem'!$C$2:$C$20,1),MATCH(1,('Data exported from acces sistem'!$A$2:$A$20=$A3)*(INT('Data exported from acces sistem'!$C$2:$C$20)=$B$1),0))

=INDEX(MOD('Data exported from acces sistem'!$I$2:$I$20,1),MATCH(1,('Data exported from acces sistem'!$G$2:$G$20=$A3)*(INT('Data exported from acces sistem'!$I$2:$I$20)=$B$1),0))

You will need to put proper dates in B1, E1, etc., and adjust the $B$1 to the appropriate date field for each day results.

Your formulas work great !
I just have to configure it to work for night shift !

THANK YOU!

Give me an example of the data that constitutes a night-shift (I think I can envisage it, but best to make sure).

Originally Posted by Bob Phillips

Give me an example of the data that constitutes a night-shift (I think I can envisage it, but best to make sure).

The worker enters IN at 23:00 on 24/07/2011 and exit the factory at 07:00 on 25/07/2011

I need the formula to compaire both 24/07/2011 (B1) and 25/07/2011 (E1) dates for OUT time and give me the last time the worker uses his badge do leave the work place.

I think the best formula would be to compaire first the 25/07/2011 and if the worker had not used his badge in that day to compaire the 24/07/2011

By doing this the formula first verify if is a night shift and if not it will compaire with the same day the worker entered.

That's about what I thought, so try this as the time out formula

=INDEX(MOD('Data exported from acces sistem'!$I$2:$I$20,1),
MATCH(1,('Data exported from acces sistem'!$G$2:$G$20=$A3)
*((INT('Data exported from acces sistem'!$I$2:$I$20)=$B$1)+(INT('Data exported from acces sistem'!$I$2:$I$20)=$B$1+1)),0))

(I see you already cater for the midnight overlap in your totals formula).

Originally Posted by Bob Phillips

That's about what I thought, so try this as the time out formula

=INDEX(MOD('Data exported from acces sistem'!$I$2:$I$20,1),
MATCH(1,('Data exported from acces sistem'!$G$2:$G$20=$A3)
*((INT('Data exported from acces sistem'!$I$2:$I$20)=$B$1)+(INT('Data exported from acces sistem'!$I$2:$I$20)=$B$1+1)),0))

(I see you already cater for the midnight overlap in your totals formula).

Yes I solved the total formula !

The formula works but now is a new problem ?

IF a person hadn't worked in 24/07/2011 but on 23/07/2011 and 25/07/2011 the IN formula (in formula infigureted by me for night shift) brings the IN time from 23/07/2011 and the out formula brings the OUT time for 25/07/2011 and the worker will have and IN and OUT time for a day in wich he did not worked.

Another problem:
If a person has more then one OUT times in a day:

John Smith OUT 15:00
John Smith OUT 16:00
John Smith OUT 17:00

I need the OUT TIME formula to give me the last OUT TIME (17:00) for a person in a day not the first.
How do I modify this formula to do that:
=INDEX(MOD(dataexport!$I$2:$I$20;1);MATCH(1;(dataexport!$G$2:$G$20=$A3)*(INT(dataexport!$I$2:$I$20)=$B$1);0))

Can you post an updated workbook with examples of these new situations (before and after)?

Originally Posted by Bob Phillips

Can you post an updated workbook with examples of these new situations (before and after)?

I've attached a file; look at the cells that are hilighted I want the formula to get the last time the worker uses his badge in a day for OUT TIME !

I am confused. John Smith clocks in and out on two separate times on the same day. How can he do that, how do we know which is correct?

Also, can we re-format the dataexport data? If you import it, from another app, could we add some code to calculate the staffmanager sheet, the formulae are getting cumbersome.

Hi I have a worksheet. It has got in time and out time. I want to how much an employee before 03:30pm and after 03:30pm. e.g. Clock In - A1-12:30 pm and Clock Out A2-06:20 pm. The result should be AM Hours = 3.0 and PM Hours = 2.50. Would appreciate if someone helps me out with the formula.