Hi guys
I am having a problem with creating a formula for a timekeeping report that I need to accomplish.
I have four columns in total. column A contains employee ID number, column B contains log entry dates, Column C contains log entry times, and Column D contains schedules.
A B C D
ID Log date Log time Schedule
12345 June 1 2010 5:49 21:00
12345 June 1 2010 6:05 21:00
12345 June 1 2010 20:35 21:00
12345 June 1 2010 21:55 21:00
12345 June 1 2010 23:32 21:00
12345 June 2 2010 3:38 21:00
12345 June 2 2010 6:15 21:00
12345 June 2 2010 20:15 21:00
12345 June 2 2010 20:48 21:00
12345 June 2 2010 21:08 21:00
12345 June 3 2010 5:22 21:00
12345 June 3 2010 6:13 21:00
12345 June 3 2010 20:25 21:00
12345 June 3 2010 20:58 21:00
12345 June 3 2010 21:32 21:00
12346 June 1 2010 12:22 13:00
12346 June 1 2010 12:45 13:00
12346 June 1 2010 16:22 13:00
12346 June 1 2010 19:25 13:00
12346 June 1 2010 22:12 13:00
12346 June 2 2010 12:45 13:00
12346 June 2 2010 15:55 13:00
12346 June 2 2010 18:25 13:00
12346 June 2 2010 21:02 13:00
12346 June 2 2010 22:22 13:00
12346 June 3 2010 12:56 13:00
12346 June 3 2010 14:15 13:00
12346 June 3 2010 16:22 13:00
12346 June 3 2010 18:04 13:00
12346 June 3 2010 22:06 13:00
OK so here's what I want to happen, I want to create a formula in Column E which would calculate the nearest log time based from the employee number, the log date, and the schedule. for example, for employee number 12345 the nearest log time for June 1 should be 20:35. I would then make a conditional format for column C that a cell would be highlighted whenever it has an equivalent value on column E so that when I drag the formula down on column E, specific cells will be highlighted on Column C. these highlighted once will then be considered the first log-in swipe for the day.
this is an example of what would appear when I drag the formula down
A B C D E
ID Log date Log time Schedule First log
12345 June 1 2010 5:49 21:00 20:35
12345 June 1 2010 6:05 21:00 20:35
12345 June 1 2010 20:35 21:00 20:35
12345 June 1 2010 21:55 21:00 20:35
12345 June 1 2010 23:32 21:00 20:35
12345 June 2 2010 3:38 21:00 20:48
12345 June 2 2010 6:15 21:00 20:48
12345 June 2 2010 20:15 21:00 20:48
12345 June 2 2010 20:48 21:00 20:48
12345 June 2 2010 21:08 21:00 20:48
Also, please take note that if 2 log-in swipes within a day are very close to the schedule (one before schedule and another is after schedule), the formula should recognize the one BEFORE the schedule. this is to prevent confusion that an employee is late even if he is not. One example is June 2 of employee 12345, 21:08 is closer to 21:00 than 20:48 but the formula should recognize 20:48 as the first login.
I have tried various formulas but to no avail. if someone here could help
Bookmarks