Extract LATE/TARDINESS per employee based on TIME IN and WORK SCHEDULE

Not sure if I'd be on point with my description here but...

I need to extract the number of minutes when an employee was late/tardy. Then, have those lates minutes be "tallied and summed up" on a separate section.
> In the attached file, RAW tab is the actual appearance of the spreadsheet that is downloaded from our time management system. I highlighted the essential columns for this query.
> Note that only 23-Jan is shown in RAW.
> Late and Tardiness columns contain the same values.
> Undertime is also considered as Late/Tardiness thus, should be added.
> ATTENDANCE SPREADSHEET is the "desired presentation" of the report.
> SHIFTS Tab are the existing shifts for the Medical Department

Please see file attached for your perusal.

I'm having this dilemma because our time management system is not considering ≤15 minutes - from the scheduled start of shift - as late/tardy.

How do I extract the time in this string?

CW - Day Shift - 8:00 AM to 5:00 PM

8:00 AM is the employees scheduled time in, while 5:00 PM is the scheduled time out.

Can you give a step by step example of how to clock one employees time.

For instance, take Employee "''31"

Looking at RAW, I see Employee 31 clocked in at 12:18 and clocked out at 15:50. I get a total work time of 212 minutes from this. Yet, the hours worked, shows 2.83 (169.8 minutes). How do you account for the difference between 212 and 169.8?

As for the time string question, I don't really know of an easy way to extract that from what is given. Is there any way you can standardize what is in Column AG "Work Schedule"; what is in there is sort of all over the place and I think will require some hefty coding to extract data from it (it would be much easier if the cell data is all in a similar format). A format like that of AG2 "CW - Day Shift - 8:00 AM to 5:00 PM" would be a good one to use if you could keep that in a consistent format. Though, I'm not entirety clear what is is your trying to do by extraction.

Thanks for the reponse.

Regarding the capture of an employees worked hours, what we do is:
1. The moment we arrive, we have to ensure we log into our 3rd party time management system and TIME IN
2. Within the shift, ideally, we should also be "clocking in" our 2 15-minute breaks and 1 hour unpaid lunch break, but we arent really that strict with it yet.
3. After our shift, we log back in to the time management system and hit TIME OUT

Great catch on employee 31's hours. Regarding that, even if the employee doesnt hit LUNCH IN and LUNCH OUT within his shift, the system prioritizes the deduction of the 1 hour lunch from his worked hours. A single shift consists of:
> 7.5 production hours
> .5 paid Break Time minutes (2 15-minute breaks)
> 1 hour unpaid Lunch Break
A total of 9 hours (or 540 minutes)

So looking at Employee 31's logs for that day, he was only "allowed" to be paid a maximum of (approx.) 170 minutes since:
(540 minutes) - (60 minutes lunch) = 480 minutes left for the shift
(480 minutes) - (240 minutes late log in) = 240 minutes left for the shift
(240 minutes) - (70 minutes undertime) = 170 minutes

I really can account for the 0.2 minutes (since the Hours Worked showed 2.83 or 169.8 minutes) but that's roughly how Employee 31's hours were calculated for that day.

Ragarding the shift format, that's also my big dilemma. If you saw the SHIFTS Tab on the Sample File, those were/are the available shifts that were used by my department. Some shifts are missing dashes spaces and have extra or missing characters/values from the usual schedules. And unfortunately, these shift descriptions are something that we cant readily "edit" or standardize since these are set by the 3rd party company.

What I temporarily did while waiting for a possible resolution from this forum was:
> Pre-assign a column to have the hh:mm:ss time format;
> Then on another column, input an IF, OR, HOUR, MINUTE formula so that
> When I paste (as values) the raw data on to the "dump" spreadsheet, I would just have to manually do Text To Columns to column AD (Work Schedule).

But its still quite a handful since, as you've noticed, some schedules are different from the others. Not to mention a FLEX Sched like mine.

I attached the updated Sample File for your perusal.

Thanks for your time.