Filtering the log in and log out time

[Eusoj]

Need help please,,,

Been trying a lot of formulas from the forum but still can't get the result I want. I want to filter the time where there is a standard time in and time out in the morning, noon and end shift. Raw data are collected from a registering machine and somehow there are multiple records in just one person and sometime there are missing record. I had posted the spreadsheet and in the time in time out are the time exactly what I want to formulate highlighted in yellow.

Given in the punch time,

In the Morning Time in, If time in is less than standard time it should give me standard time otherwise the log in time.
In the Morning Time out, any log between 1200-1300 hours it should give me the standard time out otherwise the log out time.
and goes same with the Afternoon time in and time out.


Hopefully somebody can give me a guidance. Thanks..

[juddaaaa]

I've taken a stab at it. Let me know if that's what you meant

[Eusoj]

Thank you but it is not like it.

So,

Column G (Time in) should be from 0645-1200 hours
Column H (Time out) should be <=1200 hours
Column I (Time in) should be >=1300 hours
Column J (Time out) should be from 1300-1845 hours

Challenge I am having is the punch time which doesn't record in 4 columns only.
Some instances, from punch1-4 they are all falling into the column(time in) G. That's where distorting my formulas when i am moving to time out column.

Thanks.

[Gregb11]

I think the attached will get you closer, but you need more logic. If there are only 3 punches, where's the cut-off to determine if they missed the clocking in after lunch, or if they missed the clocking out at the end of the day.

In other words, if the 3rd punch is 15:00 and there is no 4th punch, is that a late clock in from lunch, or did they miss the clock-in from lunch and that's their early clock out time?

[Eusoj]

Hi Gregb11,

Thank you so much.

To answer this ""In other words, if the 3rd punch is 15:00 and there is no 4th punch, is that a late clock in from lunch, or did they miss the clock-in from lunch and that's their early clock out time?"",
it is the early clock out time.

Looking at your formula in the AM Time out, Punch2 sometimes is not where the clock out is. These could be still morning clock in and evening clock out. Same in the afternoon Time in.

Meaning at all the punches log, it could be any of them falls into to the column AM time in/out and PM time in/out and sometimes blanks log are there.

[Gregb11]

To answer this ""In other words, if the 3rd punch is 15:00 and there is no 4th punch, is that a late clock in from lunch, or did they miss the clock-in from lunch and that's their early clock out time?"",
it is the early clock out time.

It was just an example - so at what time does it change from an early clock-out to a late clock-in? If the punch-time is 13:50, it's probably a late clock-in, not an early clock-out, no? And in this case they missed punching out for the day?

Punch2 sometimes is not where the clock out is. These could be still morning clock in and evening clock out. Same in the afternoon Time in.

So what's the rule?
I'm assuming the first punch is always regarded as the AM clock-in time.
The second punch could either be the AM clock-out time, or they missed that punch and it's the PM Clock-in time, or they missed that punch and it could be the clock-out time.
The 3rd punch could be the PM clock-in time or they missed the punch in and it's actually the clock-out time.
The 4th punch I would assume is always assumed to be the PM clock-out time.
Would they clock-in/out more than 4 times? If they could clock in and out for breaks as well, then it would be very difficult to determine what's a clock-in/out vs. a missed punch.