Time formula - dont know what else to call it...please read!!

See test file and follow along...i'm in a jam at work.


As you can see on the test file, i have 4 working shifts for one employee.

This person clocks in at 7:30am, then starts their break at 9am

They get a 10 minute break and end their break at 9:10am

They then work until their lunch starts at 12:30pm

They take lunch for 30 minutes, then clock back in to work at 1pm

They work until 2:30pm, take a 10 minute break until 2:40 and work again until 4pm

I have the exact information from my payroll system as you see here for 100 other employees.


My boss though doesnt care what time their working shifts are...my boss wants to see what time their non-working shifts are!

So this creates a problem for me to go through and manually input this in.

Basically, in column C i want it to read BREAK 9am - 9:10am
LUNCH 12:30pm - 1pm
Break 1pm - 2:30pm

i need a formula for the other 99 people

thanks
ANY HELP IS MUCH APPRECIATED