Help to find control logic - Employee hours control

[Qete_ARG]

Hi everybody, I am trying to find a way to calculate the amount of hours worked by every employee. Each employee must chek in and out 2 times a day (we get one hour to lunch).
The trick here is to find a way to calculate the amount of hours worked during the morning and the afternoon, but I cannot use the column named "Item". It is not trustable.
For example, on march 11th the employee checked out at 17:57 but one time as "In" and one time as "Out", and on march 31st the employee didn´t check in but checked out at noon.
I was trying to figure out a way to control this by a formula, but I have not been able to find the way to do it!!
If someone here could give me a hand, I would be really appreciatted.

Excel Workbook
	A	B	C	D	E	F	G
31	EmployeeId	EMployeesName	Date	Item	DayNumber	MonthNumber	Year
32	111222333	Employee1	10/03/2010 08:19	In	10	3	2010
33	111222333	Employee1	10/03/2010 13:01	Out	10	3	2010
34	111222333	Employee1	10/03/2010 13:43	In	10	3	2010
35	111222333	Employee1	10/03/2010 18:01	Out	10	3	2010
36	111222333	Employee1	11/03/2010 08:33	In	11	3	2010
37	111222333	Employee1	11/03/2010 13:07	Out	11	3	2010
38	111222333	Employee1	11/03/2010 14:06	In	11	3	2010
39	111222333	Employee1	11/03/2010 17:57	In	11	3	2010
40	111222333	Employee1	11/03/2010 17:57	Out	11	3	2010
41	111222333	Employee1	31/03/2010 12:59	Out	31	3	2010
42	111222333	Employee1	31/03/2010 13:58	In	31	3	2010
43	111222333	Employee1	31/03/2010 18:00	Out	31	3	2010
Hoja2

 

[Gerald Higgins]

No formula is going to give you a good solution for examples like the one where they checked out, without checking in first.

At best, I would suggest getting a formula to highlight data with a problem, which would then prompt manual intervention.

 

[Qete_ARG]

You are right....I was afraid I was gonna have to think of a VBA solution in order to get this done...Do you think with VBA would be plausible? I will have to take off the dust to my little VBA experience and give it a try!
Thanks for your response!

 

[Gerald Higgins]

Sorry, instead of "No formula ..." I should have said "No computer programme ...", including VBA.

If you can't rely on the data, I don't see how you can write any kind of programme to deal with the data correctly.
The best you can do, I think, is highlight data that doesn't meet (a) certain pattern(s).
For example, you might say,
IF an employee has exactly 4 transactions on a certain date, assume the data is correct, and calculate the hours
ELSE flag that that employee / date combination has data that looks incorrect, and requires manual intervention.
The way I've seen this done in practice is you ask the person what time they actually started that day. But you can't expect a computer to work that out.

This in itself would not cover situations like this...
Let's say the employee does NOT clock in first thing in the morning (like they did on 31st March), they then clock out at lunchtime, back in after lunch, and then in AND out at 17:57, like they did on 11th March.
In this situation, you would have 4 clocking on/off events, and it would not be highlighted by the test I outlined above. But the data is clearly wrong.
So you could add another rule to say, for example, highlight any occasions when there are two or more clocking events within a minute of each other (or within 5 minutes, or 10 minutes, or whatever is appropriate to your situation), and prompt manual intervention.

There may possibly be other errors in the data that you might want to try and trap as well.

 

[Qete_ARG]

I am really appreciatted with you for taking the time to give me an opinion.
I agree that the best solution is to flag the days that need some user intervention in order to analize them. I was afraid this was the only solution, and I confirm it is!!

Thanks a lot!!
Miguel

 

[Norie]

Miguel

I agree with Gerald, though you might be able to restrict what people enter using some data validation.

That could get a bit complicated mind you, so perhaps best to just highlight problems.

 

[Qete_ARG]

Hi Norie, thanks for resplying!
The thing is that this data is taken from the fingerprint reader where all the employees check in and out...so I am not able to restrict user input.
But thanks anyway!