Break/Aux details not matching for Night shift Agents as Date Over laps

[KARTHICKNAIDU]

Hello,

I'm trying to prepare break adherence report for list of agents where their break time allotted for the day will be 1Hr which includes ( Short break, Lunch break and Undefined breaks only ) rest of the breaks will be considered under Exceptional breaks.

As per attached sample data I'm done with the report. But a challenge is back of me every time regarding the date overlapping for nightshift agents.

If have a data with when agent went for break and came back means as like login/logout, then it is easy to calculate the date and time. But in this data i have only Time interval by when agent taken break and duration of the break in Secs.


I have 2 scenarios:

1 scenario: If agent shift starts by night 10:00 PM and ends by 07:00 AM by the next day, then need to calculate the total breaks in between that duration.
2 Scenario: If an agent doing Over time even then i need to calculate break time only for the scheduled shift time and to show the break time separately while in OT hours.


Can anyone give me a solution for the same where i can get the total sum of break under particular break category even for night shift agents.

My working sample file attached of your clearance

[KARTHICKNAIDU]

Can anyone help me in sorting out this concern plz....