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 control access sistem export data and time in the same column like 24/07/2011 06:35:00 and when I want to extract the time I need to do a look up after the date. Who can I do this if date and time are in the same cell. If you have any questions about what I'm tryng to do here is the attached excel file

 

[Hermanito]

Datetimes in Excel are internally kept as a double.
The date is the integral part, the time is the decimal part.

If your date is in cell A1 then
'=FLOOR(A1;1)'
will give you just the date, and
'= A1 - FLOOR(A1;1)'
will give you just the time.

I cannot download your file here, so I hope my post makes sense

 

[alinpion]

If your date is in cell A1 then
'=FLOOR(A1;1)'
will give you just the date, and
'= A1 - FLOOR(A1;1)'
will give you just the time.

This formula works fine !

Now I want to extract from one sheet to another the first time a person uses his badge in a day

I got something like this; (sheetA)
A B C D
John Smith IN 24/07/2011 06:35
John Smith IN 24/07/2011 06:36
John Smith IN 24/07/2011 08:20

In sheetB

A B C D
Name Date Tiime worked
24/07/2011
IN OUT
John Smith 06:35 (auto extracted from sheetA)


In sheetB I want to extract from sheetA the first IN TIME for a person in the the day specified under date (B3) the formul would look something like this =IF( Name (A5) and date (B3) from sheetB found in sheetA ; GET IN TIME ; " " )

 

[Hermanito]

Not sure if that is still possible using only formulas, but I've been surprised by the formula experts here before... so I'm gonna wait a bit before I propose to solve this with VBA

 

[alinpion]

Not sure if that is still possible using only formulas, but I've been surprised by the formula experts here before... so I'm gonna wait a bit before I propose to solve this with VBA

For my previous post another solution would be =IF( =IF( Name (A5) and date (B3) from sheetB found in sheetA ; GET MIN of the IN TIME ; " " ) meaning if both name and date from sheetB corespond with name and date from sheetA get the first time he uses hi badge in that day.

If you have VBA solution please be free to post it !!

 

[GlennUK]

How about:

Excel Workbook
	A	B	C
1	John Smith	IN	24/07/2011 06:35
2	John Smith	IN	24/07/2011 06:36
3	John Smith	IN	24/07/2011 08:20
4
5	24/07/2011
6	IN
7	John Smith	06:35
Sheet

 

[alinpion]

How about:

Excel Workbook
	A	B	C
1	John Smith	IN	24/07/2011 06:35
2	John Smith	IN	24/07/2011 06:36
3	John Smith	IN	24/07/2011 08:20
4
5	24/07/2011
6	IN
7	John Smith	06:35
Sheet

The problem with your formula is that a person could use his badge for enter not just three times (c1:c3)
I need a Formula that could compaire the names and date from sheetB with those from sheetA and if matches arre found the formula should bring in sheetB the minimum IN time (the firtst time that worker uses his badge to enter) for that worker in that day.
Click on the link to see what I'm trying to do staff manager

 

[GlennUK]

My formula does a name comparison ... in the example it only does the calculation for names matching the one in cell A7 ... so there is no problem with the formula.

I cannot navigate your link due to the firewall policy where I work.

 

[alinpion]

The guys from excelforum solve 80% of my problem with this formula:

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

Only thing I need now is to modify the OUT formula to include night shift
this part it needs to be modifyed
(INT('Data exported from acces sistem'!$I$2:$I$20)=$B$1)
B1 is a cell that holds a date let's say 24/07/2011 I need the formula to compaire the data from two cells (two dates) B1 and C2 (25/07/2011)

I've tryed
=INDEX(MOD(dataexport!$I$2:$I$20;1);MATCH(1;(dataexport!$G$2:$G$20=$A3)*(INT(dataexport!$I$2:$I$20)=($B$1:$C$1));0)) but it doesn't work

Any solutions??