Excel timesheet help

[mbeach]

I?m trying to take an existing employee time sheet in Excel (Office XP) and add the appropriate formulae so that all an employee needs to do is enter the pay period start date and then the remaining dates in the relevant period will auto fill. Then, enter the daily start and end times and the time sheet will calculate daily, weekly, and overtime hours worked. Among others, some of the problems I?m having are:


*I need to keep the original format.

*(And this is the tough one) Overtime in the State of Texas does not apply until after 40hrs have been worked. Then any daily hours over 8 can be applied retroactively. So I need a timesheet that shows overtime as regular hours worked until 40 hours have been reached, then separates the daily overtime from the regular column and places it in a daily overtime column. Shouldn't be too hard to find...Right?...

I know that it?s difficult (if not impossible) to offer any suggestions without seeing the time sheet itself. Where might I find a tutorial or some examples of other Excel based timesheets?

 

[labgeek]

The overtime part isn't that hard actually. In your 2 daily hours fields create formulas that are conditional.
In pseudo-code:
Daily hours cell: if (weekly_hours_worked >= 40) and (daily_regular_hours_worked >= 8) then it's 8 else it's the real time worked.
Daily OT cell: if (weekly_hours_worked >= 40) and (daily_regular_hours_worked >= 8) then it's (real worked - 8) else it's 0.

if A1 = total worked that day and B1 = weekly total then functions would look like:
Daily regular cell: =IF(AND(B1>=40,A1>=8),8,A1)
Daily OT cell: =IF(AND(B1>=40,A1>=8),A1-8,0)


Neither is the date incrementing... If A1 is where they enter the date, and A2 the next day etc. Then A2: =A1+1 A3: =A1+2 down the line. Excel is smart enough to know that if you add 1 to a date, you want the next date.

Edit - here's a quicky ss for you to look at -
http://www.2report.com/timesheet.xls

B and C columns are decimal versions of start and end times - 9.00 for 9am, 17.50 for 5:30pm

 

[mbeach]

Thanks labgeek,
You?ve given me hope. The problem is that there are a few variables.

As I mentioned, I have to maintain the format of the original timesheet which is a semi-monthly format.

If a pay period begins on any day other than Monday (Wednesday, for example,) then weeks one and two can never equal 40 hours unless the assumption is that Monday and Tuesday were worked at 8 hours each. The formulae must make this assumption.


If you can suggest a formula that will take into account the variable work week you will be my hero.