Lookup for Shift End when Crossing Midnight

Hi All!

I am new to this forum but I hope you could help me on this one.

I have a raw data that shows the Punches of an employee for the day. The ones that cross midnight on to the next day gives me headache because included in the raw data is the start date and start time for the next day's shift.

In the example. Yuta Smith's schedule is June 8,2020 11:00 PM- June 9,2020 8:0AM.

I can capture the Start Date and Start Time. But I am having trouble capturing the End Date(F4) and End Time(G4).

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Try in cell H2 and fill down.

Formula:

Please Login or Register  to view this content.

In practice, some one come sooner and leave later schedule, the extra hour/minute should not be taken into account, for instant: In: 22:30 vs 23:00 (30 minutes earlier), Out: 8:05 vs 8:00 (5 minutes later)
IF you might interest, use below solution:

For the first row, H2:

Please Login or Register  to view this content.

Fron second row, H3:

Please Login or Register  to view this content.

Drag H3 down