Lookup value based on Time value

[Pavan Kumar Sistla]

Dear All,

Attached is the excel in which has master data with details like Date, Vehicle no., Name of the Driver, Shift Timings (Time In & Time Out) of each vehicle. There is 1st sheet in which the details like Date, Vehicle No., Time of Fine, Fine Reason are present. The Time Out and Time In are the shift timings of drivers who drive the same vehicle in different shifts.

Based on the timing of the fine, the driver name needs to be identified in the 1st sheet. For example if there are two drivers A & B and driver B starts shift at 8:00 PM, and the fine time is @ 8:20 PM, the name of driver B should be picked up in the 1st sheet.

Could you please help me in a formula to retrieve.

Thank You and Regards,
Pavan.

 

[NARAYANK991]

Hi ,

See if this is OK. I have made use of 2 helper columns.

Narayan

 

[Pavan Kumar Sistla]

Thank you Narayan. However, attaching herewith the file which is giving value error after I have increased the number of rows in 2nd sheet.

Also, could you please help me in explaining the formula which would be a great help !!!

 

[Pavan Kumar Sistla]

Could you please check the above and help Narayan.

Thank You and Regards,
Pavan.

 

[NARAYANK991]

Hi ,

Your sample file and the file you have uploaded last have significant differences , and it should be you who should look at these differences and change the formula accordingly.

One of the time entries , in row 3473 , has the text Absent instead of actual time values ; it should be clear that a formula which expects numbers will not work with text.

Lastly , the timings which have been inserted in the Report tab are not finding a match in the timings which have been inserted in the Master ; you can see this for yourself by changing the timing of the first entry from 17:00:00 to 21:00:00.

I have done what I could , and you will have to take care of these points.

Narayan