[SOLVED] Calculate time worked inside Regular Hours and Over Time

Hi.
I'm not really good with Excel so in the attached Excel file, there is no formula at all. Sorry.
I need help to create a timesheet for my employees. We've been doing it manually all these years and it's tiring to calculate each month.

I will try to explain what I want as clear as I can.

1) Core (regular) hours is from 08:00 to 17:00 hours.

2) Any hours before and after that will be considered as Overtime (1.5 X regular hours)

3) Our employees checks in and out very randomly
-for example, employee A checks in at 05:02hrs and checks out at 12:16hrs
-and then the same employee checks in again at 14:29hrs and out again at 16:37hrs.
-and once again checks in at 21:00 and out at 00:37
-all in the same day.

4) Some days they checks in and out only once in the same day. For same days, twice. It really depends on the how much work we have on that day.

5) What I want is every minutes worked in the duration between 08:00hrs and 17:00 counts as Regular Hours Worked

6) And every minutes worked outside the duration of the 9hours between 08:00hrs and 17:00hrs counts as Overtime.

7) I just need the total Regular Hours Worked & the total of Overtime Hours Worked at the end of each month.

Thanks.

In K7:

Please Login or Register  to view this content.

And drag down.

In L7:

Please Login or Register  to view this content.

And drag down.

In M7:

Please Login or Register  to view this content.

And drag down.

Make sure your formatting is set to time. These are also assuming you will not cross into another working day with overtime. If that may be the case, more work will need to be done to remedy that issue.