Adding up Absent time taking lunch time into consideration

I work at a call center and I have to report absent time for my agents.

I am working on a template that was previously saved, however, the absent hours do not account for when the person has not been to lunch yet. This is usually done as a manual update.


There are three main situations that I need my formula to account for:

1) Person that has been absent all day
2) Person that has been absent part of the day (after lunch)
3) Person htat has been absent only a few hours (before lunch)


Lunches are 30 minutes long


Whenever a person is absent or late, we put their time in two columns called ABS in and ABS out. It basically marks the start and end of their absent time.

•ABS IN is when their schedule would have started
•ABS OUT is when they arrived if they were late, or their out time if they were absent all day.


Here is what my spreadsheet would kind of look like... I need to know how many absent hours there were to calculate the actual worked hours.

I cannot simply deduct abs out minus ABS in because then I am not factoring in the lunch times.

I have attached a sample file. Thanks.

ABS tracking template v1.xlsx

try this