Find nearest Date/Time and copy

There might not be a possible answer to this but I'm hoping someone can help me speed up some input.
Tracking.xlsx
Each week for work we get data from two different sources - Direct From Engineers and Tracking Data. At the moment the two sources don't communicate so to combine the data it has to be done manually.

What I need is for the data in Sheet 2 Column A to copy across to the closest time in Sheet 1 Columns L-O for each driver. Only times when the Event (Sheet 2 column E) is either 'Ignition On' or 'Ignition Off' should be copied. I know columns L & O are blank, but these will simply be the first/last Ignition On/Off of the day.

Regarding Sheet 2;
Column G would normally contain an address (street name, county, post code) in one cell. Obviously I've had to remove that information. Columns H & I won't contain any information.
Column G - Will equal 1 if the row above OR below had the same information, will equal 0 if both above and below didn't contain the same information
Column H - Will equal UP if the row above contained the same information, will equal 0 if row above didn't contain the same information
Column I - Will equal DOWN if the row below contained the same information, will equal 0 if row below didn't contain the same information

As you can see there are occasions when more than one job was completed in a day. For these cases the Day Start is only relevant for the first job and Day End is only relevant for the last job.

Due to the address' I'm anticipating the odd error if I can get a solution into the main spreadsheet but hopefully these will be few and far between.

Hi
Your Sheet1 column M and N as a problem with date. You can convert them using

Formula:

Please Login or Register  to view this content.

in column P and Q
Then, use the following array formula (CTRL+SHIFT+END) in cell L2

Formula:

Please Login or Register  to view this content.

where P2 is your converted date.
Copy down
Use similar formula for your O column.

See the file Tracking.xlsx
Regards

I'm not looking to simply convert the dates in columns M & N, i'm looking to find the nearest matching date/time listed in Sheet 2 for the times already listed in columns M & N and copy them across to sheet 1 - As well as getting the data from sheet 2 for columns L & O

Hi DHFE
I see you do not want to convert dates, but you have to use them, or use the following formula for column L:

Formula:

Please Login or Register  to view this content.

L column already has the formula indicated in post # 2
Will is not correct?

I don't think you're understanding what I'm asking for. I didn't say I don't want the date converting, I said that's not the result I'm after - I know it needs to be done to match it to data in Sheet 2, but simply converting it isn't the end of what needs doing with that information.

Tracking2.xlsx
Columns R-U show what the result of Columns L-O should look like. (L,O,P,Q are using your formulas)
The Day Start Time can't take place after the Job Start Time (in other words they can't start their days work after they have already started a job that day).

R = Time of the First "Ignition On" that day (for that driver)
S = Closest similar time of an "Ignition Off" compared to the data already in M (for that driver)
T = Closest similar time of an "Ignition On" compared to the data already in N (for that driver)
U = Time of the Last "Ignition Off" that day (for that driver)

and as I said before, there's often more than one job per day so the Day Start and Day End times are only relevant to the first and last jobs that day, respectively.

See the file with the solution for "Day start time"

Formula:

Please Login or Register  to view this content.

and "Day end time"

Formula:

Please Login or Register  to view this content.

Regards

Tracking2.xlsx

ok, that gets me the information for L and O. Any ideas on M & N? (i.e. have them become S & T)

Originally Posted by DHFE

S = Closest similar time of an "Ignition Off" compared to the data already in M (for that driver)
T = Closest similar time of an "Ignition On" compared to the data already in N (for that driver)

Hi DHFE
Clarify me your request about the two missing columns:
For a given engineer and for a given day
Column M is a date-time for an event "Ignition Off". Which of the dates-times should I consider?
The same for the column N. Which of dates-times of event "Ignition On" should I consider?
Regards

I want the times in Sheet 2 which are the closest similar times to the ones already listed in columns M and N. For column M it must be an Ignition Off event and for column N it must be an Ignition On event. So if you look at the sheet, the first time in column M was 09:15; The closest time in sheet 2 with an ignition off event was 09:14:08 - this is the time that needs to be copied across. Similarly with column N, the time was 10:45 and the closest time in sheet 2 with an ignition on event was 10:44:48.

Columns M & N come from the engineers logs and they usually round up or down their time. The data in sheet 2 comes from our tracking service and so is more accurate. We're trying to find out how long it takes certain jobs to be completed so we can better schedule our workloads.

Hi DHFE

See the columns X and Y or Z and AA file: Tracking2.xlsx

It looks right for the most part but there appears to be some sections where the result is a #N/A, so it's not exact. I appreciate the help, you've certainly reduced the time it takes to complete the task.