Need help calculating timesheet

**Squiffer** · 06-18-2012, 08:09 PM

Hi all,

I'm creating a spreadsheet to calculate employee's timesheets and have 90% of my formulas figured out thanks to various threads on this forum. I thought I was finished when this last problem dawned on me.

On the spreadsheet (please see attached file (saved in .xls format for compatibility)) I have gotten to the stage where I can enter a start and finish time for any given day and the formulas will work out regular hours, overtime hours and total hours for that day. I am calculating regular & overtime hours on a per-day basis. In this case my regular hours are as follows:

8 hours per day (9am - 5pm)
40 hours per week (Mon - Fri 9am - 5pm)

I also have fields to calculate my total weekly regular, overtime and total hours. I am then calculating my wage based on these totals.

My problem is this:

I enter a 9am start time and a 6pm finish time for Monday - Friday, which gives me 8 hours regular & 1 hour overtime per day and a weekly total of 40 regular and 5 overtime hours. Perfect.

I enter a 9am start time and 6pm finish time for Saturday, and because my spreadsheet doesn't know I have a cut-off of 40 hours weekly regular time, this in turn updates my total weekly regular hours to 48 and total weekly overtime hours to 6.

What I want:

To let the spreadsheet know that I also have a cut-off of 40 regular hours per week, and everything above this is overtime.

I would really appreciate it if somebody could take a look at the spreadsheet and help me out a bit here.

Sorry for the long post and I hope I've been clear enough. Thanks in advance!

**TMS** · 06-18-2012, 08:30 PM

You could maybe have a hidden helper column and have a running total of the basic hours. Then you could check if the running total for the previous row had reached 40 and, if so, all the hours go into the overtime column. You'd have to adjust the formulae in both columns.

Sorry, I'm using an iPad so I can't work it out for you.

Regards, TMS

**ncmay** · 06-18-2012, 08:49 PM

See if sheet2 of the attached workbook does what you want.

**Squiffer** · 06-19-2012, 09:13 AM

Originally Posted by ncmay

See if sheet2 of the attached workbook does what you want.

That's fantastic - thanks very much, it's much appreciated.

Just one small issue with this: If a row has hours worked that are less than the regular amount, these hours are counted towards the total regular hours as well. E.g. If I enter a 9am start time and 1 pm finish time, it updates the fields to show I have worked 4 hours regular time that day and 44 hours regular time that week.

Please see Sheet 2 for an example of what I'm explaining.

Thanks

Originally Posted by TMShucks

You could maybe have a hidden helper column and have a running total of the basic hours. Then you could check if the running total for the previous row had reached 40 and, if so, all the hours go into the overtime column. You'd have to adjust the formulae in both columns.

Sorry, I'm using an iPad so I can't work it out for you.

Regards, TMS

This could have worked too, thank you for your suggestion.

**ncmay** · 06-20-2012, 08:22 AM

The attached file should give you several options to choose from.

Overtime after 8 hours daily
Overtime after 40 hours

**Squiffer** · 06-20-2012, 03:50 PM

Originally Posted by ncmay

The attached file should give you several options to choose from.

Overtime after 8 hours daily
Overtime after 40 hours

Again, amazing.

I chose the "overtime is applied if total hours so far surpasses 40" version.

I just had to make one small adjustment to your formula, which was to make the if statements check if the total hours so far is <= than the cut off rather than just <. Other than that yours is flawless.

Thanks very much for your help, much appreciated! I would have never figured it out on my own.