Time range between a certain time range and how many hours of that certain range

[AdinelB]

Hello, everybody

I'm trying to automate our employee entries and I faced the following problem.

So if Employee 1 is working rom 12:00 to 20:00, theoretically it's a 8 hours program so it's no overtime.

But what if every hour between 18:00 and  07:00 AM is paid different (better) . What formula should I use to check a time range between another fixed time range 18:00-07:00 for example and how much hours has he been worked from that certain range. In this case, 2 hours.

Please be aware that our employee works also night shift hours, between  22:00 current day and 05:00 the upcoming day. This is a tricky range. I guess we should use the MOD Function for this one

Thank you in advanced. Any help is much appreciated!

[#2 · Accepted solution]

@AdinelB 

See the attached demo workbook.

[#3]

Sir,
You just saved me big times. I've been working really hard the last few hours on it.

Thanks a lot!