Weekly Timesheet

Rourke Waring · May 21st, 2002, 04:40 PM

I am trying to set up a spreadsheet that totals weeekly hours (in rows) by employees (columns). It must take into account that any hours over 8 per day and over 40 per week are charged at a different rate. The only vaues that the user will be able to change are the hours per day and the regular and overtime rates. Thanks

Yogi Anand · May 21st, 2002, 04:53 PM

Quote:

On 2002-05-21 15:40, Rourke Waring wrote:
I am trying to set up a spreadsheet that totals weeekly hours (in rows) by employees (columns). It must take into account that any hours over 8 per day and over 40 per week are charged at a different rate. The only vaues that the user will be able to change are the hours per day and the regular and overtime rates. Thanks

Have you started working on setting up the worksheet? Are you having a problem using a specific function or formula. Setting up the entire worksheet is too broad a topic -- if you ask a specific question -- I am sure you will get plenty of response.

Regards!

PaddyD · May 21st, 2002, 04:55 PM

Rourke,

Welcome to the board!

A simple calculation for an individual weekday is:
=IF(C1<8,C1*A1,(8*A1)+(C1-8)*B1)
(If normal rate/hr is in a1 and ovetime in b1, and hours worked one a day in c1)

Hours over 40/ week in total must be made up of days where over 8 hours were worked.
If not, people are working at overtime rates on the weekend, so all weekend hours are at overtime rates - or are there further complications?

Hope this helps

Paddy

Brian from Maui · May 21st, 2002, 05:19 PM

Have a look at this site

http://www.cpearson.com/excel/overtime.htm

cblincoln43 · May 22nd, 2002, 10:51 AM

Look in your e-mail,it will give you a good start. and again any thanks go to ALL the fine folks on the board who gave me so much time in getting it to work and are still putting up with me.

Rourke Waring · May 22nd, 2002, 11:34 AM

Thanks for the help.
There is one further complication, however.
the requirements for overtime are :
over 8 hours on any day and any hours beyond 40 regular hours for a 7 day week. Employees are working irregular shifts. So, for example, if you have hours from sat to the following weds totaling 36, you will have 4 hours banked. If the employee works for another 6 hrs thurs, 4 are at reg rate, and 2 at overtime. The problem is that all cells must do the calculation; each employee's week may start at on a different day.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 04:40 PM	#1
Rourke Waring New Member Join Date: May 2002 Posts: 5	I am trying to set up a spreadsheet that totals weeekly hours (in rows) by employees (columns). It must take into account that any hours over 8 per day and over 40 per week are charged at a different rate. The only vaues that the user will be able to change are the hours per day and the regular and overtime rates. Thanks

May 21st, 2002, 04:55 PM	#3
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	Rourke, Welcome to the board! A simple calculation for an individual weekday is: =IF(C1<8,C1A1,(8A1)+(C1-8)*B1) (If normal rate/hr is in a1 and ovetime in b1, and hours worked one a day in c1) Hours over 40/ week in total must be made up of days where over 8 hours were worked. If not, people are working at overtime rates on the weekend, so all weekend hours are at overtime rates - or are there further complications? Hope this helps Paddy

May 21st, 2002, 05:19 PM	#4
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Have a look at this site http://www.cpearson.com/excel/overtime.htm

May 22nd, 2002, 10:51 AM	#5
cblincoln43 Board Regular Join Date: Mar 2002 Posts: 204	Look in your e-mail,it will give you a good start. and again any thanks go to ALL the fine folks on the board who gave me so much time in getting it to work and are still putting up with me.

May 22nd, 2002, 11:34 AM	#6
Rourke Waring New Member Join Date: May 2002 Posts: 5	Thanks for the help. There is one further complication, however. the requirements for overtime are : over 8 hours on any day and any hours beyond 40 regular hours for a 7 day week. Employees are working irregular shifts. So, for example, if you have hours from sat to the following weds totaling 36, you will have 4 hours banked. If the employee works for another 6 hrs thurs, 4 are at reg rate, and 2 at overtime. The problem is that all cells must do the calculation; each employee's week may start at on a different day.