# Need Formula to Calculate Overtime from Daily hours for a whole week

1. ## Need Formula to Calculate Overtime from Daily hours for a whole week

I need help to calculate overtime hours from daily time entries.
Normal hours are 7.6 per day
Time 1/2 is hours over 7.6 but no more than 2 hours
Double Time is all hours over that.

I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.

Wed Thur Fri Sat Sun Mon Tues Total Normal Hours Time 1/2 Double Time
10 10 10 6 8

If anyone can help it would be much appreciated.

2. ## Re: Need Formula to Calculate Overtime from Daily hours for a whole week

Here's something I threw together for you.

Hide the columns with formulas so only the day fields are showing and when they place the hours into the day columns it will automatically work out the split between hours basic, 1/2 pay and double time.

The formulas in columns AF4:AH4 are set to work out hours time hourly wage (set in cell AE2), if you only want the hours then remove everything after the close brackets in cells AF4:AH4.

Hope that helps

3. ## Re: Need Formula to Calculate Overtime from Daily hours for a whole week

Thank you so much, this will work perfectly. I did not think to hide the columns.

4. ## Re: Need Formula to Calculate Overtime from Daily hours for a whole week

After I posted I thought of this as well.

I have added the hourly rate before the name and calculated by hours then summed the total multiplied by hourly rate at time, time 1/2 and double where relevant. This will provide more transparency with calculations for audit purposes.

You can then hide each persons hourly rate by hiding column B as well and make each line specific to each person wages.

5. ## Re: Need Formula to Calculate Overtime from Daily hours for a whole week

Originally Posted by KazzICC
Thank you so much, this will work perfectly. I did not think to hide the columns.
You are more than welcome, just remember to click "Thread Tools and mark the thread as solved and click the little star and let everyone know how great I am

6. ## Re: Need Formula to Calculate Overtime from Daily hours for a whole week

Thank you Sc0tt1e

There are currently 1 users browsing this thread. (0 members and 1 guests)