Excel staff manager ! Help please !!

[alinpion]

Hello!

I'm using excel 2003 and I'm trying to make a excel staff file that would manage the time worked by employees. The data is exported from a acces sistem (the sistem just export in excell files the time when a worker entered and exit he does not calculate the total time spent). I've attached a excel file that with an example of what I want to do :

- in the sheet "Data exported from acces sistem" I'll paste the results from the access sistem
- in the sheet "Staff manager" I'll have just one table (the one who auto extracts the data from "Data exported from acces sistem" sheet); in the example I give you 2 tables first is where I want the formulas and the second is an example on who the first one should look like

In my opinion the formula for IN time for a person should be something like: IF(OR(A1(name)=names!) ; (B1 (just data(24/07/2011))=data!)) ; GET IN TIME ; " " )

If you have any ideas please be free to post it!!

 

[alinpion]

The formula for OUT time for night shifts is:

=INDEX(MOD('dataexport'!$I$2:$I$20,1),MATCH(1,('dataexport!$G$2:$G$20=$A3)*((INT('dataexportm'!$I$2:$I$20)=$B$1)+(INT('dataexport'!$I$2:$I$20)=$B$1+1)),0))

The formula works but if 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.

I need to integrate in the formula this condition IF IN TIME is on the previous day (23/07/2011) and OUT TIME is on next day (25/07/2011) (this means that the person had not worked on this day 24/07/2011) then do not extract any data.

 

[alinpion]

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))

 

[alinpion]

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))

Another ideea to solve this problem is to rewrite the formula fot OUT TIME to get the max OUT TIME in a day, but I don't know who to do that .