Formula to Calculate Overtime Hours

I've been using a time sheet I built in Excel for some time that uses Bi-Monthly pay periods. My employees get paid (hourly+#of jobs completed, + upsales). Recently, we implemented new hours for our employees and I need to track/pay overtime. Here's a screenshot of what I'm working with, then I'll explain the problem.

I am not able to insert an image.
http://i.imgur.com/OrSEK8C.jpg

I change the month/pay period and the sheet will automatically change the days listed in 'Day'. This is where the problem lies. Some pay periods start with Monday, some Wednesday, etc... Some pay periods also end in the middle of the week.

Originally I wanted a formula in cell E24 to calculate overtime hours between Monday - Saturday. But if I do that, I will have scenarios where I need to use the previous time period to calculate overtime. Any ideas for this formula?

Edit: The more I think about this, I'm realizing most solutions I have will just raise new problems. Maybe the best solution to automate overtime calculations is to switch to Bi-Weekly pay, instead of Bi-Monthly, which would require a new sheet. Or I just need to manually calculate overtime for each employee, every pay period, ugh. Any other solutions?

What are the conditions to decide what is classed as overtime?

If you click the 'Advanced' button when typing your reply, then click the paperclip in the top row of icons above the editor window, then you can attach a sample excel file instead of posting images.

If you type in the expected results, it helps us to see what you are trying to do.

Attached is a copy of my basic workbook.

Everything works perfectly, except for overtime, which is calculated in D24:F24.
Currently I have a formula in E24 that calculates overtime for any hours over 80 in the pay period. This is not correct.
It needs to be any hours over 40 per week (Mon-Sun).

Refer to my first post for more details.

Thanks

@FDibbins, We pay bimonthly, not biweekly. The imgur is only halfway filled out.

If you change the Month, Pay Period, or Year, you'll see 'Date' and 'Day' will change accordingly. So each pay period will start and end on a different day, but I need to pay overtime for each period of Monday-Sunday.

What do you mean? If total hours between Monday and Sunday is over 40, I need to add that amount in overtime pay. Most pay periods will start somewhere in the middle of the week, so I will need to reference some data from the previous pay period.

Right now, I manually go through each employee and add overtime pay to each paycheck. I am searching for a way to automate this, given the data I already have.

Maybe this will make more sense.

Red = Pay Period 1
Green = Pay Period 2

30 Mon
31 Tue
1 Wed
2 Thu
3 Fri
4 Sat
5 Sun
6 Mon
7 Tue
8 Wed
9 Thu
10 Fri
11 Sat
12 Sun
13 Mon
14 Tue
15 Wed
16 Thu
17 Fri
18 Sat
19 Sun
20 Mon
21 Tue
22 Wed
23 Thu
24 Fri
25 Sat
26 Sun
27 Mon
28 Tue
29 Wed
30 Thu

For Pay Period 1 I need to pay overtime for (30 Mon:5 Sun) and (6 Mon:12 Sun).
For Pay Period 2 I need to pay overtime for (13 Mon:19 Sun) and (20 Mon:26 Sun).

The hard part for me is creating a formula that works in every situation (First day of pay period is Mon, Tues, Wed, etc...)

Where is the data for the hours coming from? Is there a hidden sheet that holds all of the history?

Without a more practical pay frequency such as 2 weekly, the only thing that comes to mind is to assume that all employees work 8 hours every weekday and anything over that, or at weekends is overtime and use that to calculate on a daily basis.

You could use historical data from the previous period to work out overtime for the part week at the beginning of the current period, but unless you can see into the future, you will have nothing to refer to in order to accurately calculate the part week at the end that runs into the next period.

You could offset the overtime and use data from the current and previous period to calculate it, (for example, overtime paid from the Monday on or before the first day in the current period until the last sunday in the current period, with anything after that being carried over to the next), but I think that would get messy too.