closest match from multiple criteria

**wongja** · 07-29-2010, 05:04 PM

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

**DonkeyOte** · 07-30-2010, 01:38 AM

Originally Posted by wongja

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

The fact that you are not in fact looking for the closest match per se makes this calculation less intensive (ie no need for ABS array test).

If we assume per the sample that the data is sorted by ID, Log Date, and Log Time (in that order) then

Please Login or Register  to view this content.

The LOOKUP calc is only performed once per ID (first) - all subsequent instances simply use the first result.
(you could restrict the LOOKUP range further using embedded INDEXs but I would say they're probably not worthwhile)

**wongja** · 07-30-2010, 06:23 PM

Thanks! this formula worked but it returned some unexpected results.

Since it prioritizes the "before schedule" log than the "after schedule" log, some values returned are way off the expected results.

can you make it so that it now returns the closest match? I believe I can make a more accurate report by using that

**DonkeyOte** · 07-31-2010, 01:37 AM

Originally Posted by wongja

Since it prioritizes the "before schedule" log than the "after schedule" log...

Yes, per your requirements.

Originally Posted by wongja

can you make it so that it now returns the closest match? I believe I can make a more accurate report by using that

Please Login or Register  to view this content.

Again, as before, the main calculation is performed only once per combination of ID & date - subsequent records use the previously calculated value - this assumes the data is sorted by ID & date.

Given the assumption that the data is sorted you can in theory embed some INDEXes in there to reduce the range size being processed in the Array - whether or not that is worthwhile will depend on the size of the original data set.