Discussion on:
View:
Show:
Use a list to define quarter-hourly (or other) timeslots. Allow for more than a lunch in the day. Allocate and sum project codes.
www.helpdesk.net.au/downloads/Afras.xlsx if an offering for any that want to build on it
www.helpdesk.net.au/downloads/Afras.xlsx if an offering for any that want to build on it
A Very good breakdown of a how to. I applaud your site and it help to the people like myself whoare self taught in both software and hardware. Thanks a whole lot. I will be using this for myself and my sister in laws business.
Better check the formulas for calculating the hours for each day.
The same formula =IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24) is used for both the first 8 hours and for the overtime.
The overtime calculation should be:
=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)
The same formula =IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24) is used for both the first 8 hours and for the overtime.
The overtime calculation should be:
=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)
I noticed the same issue but validated tht the downloaded file has teh correct formula
This is great and doesn't seem so "simple" to me However, I also have to compute (during the same work week) any compensatory time our non-exempt employees earn each week. Whereas overtime is total hours over 40 that the employee actually works, our employees accrue compensatory time when any combination of work, vacation, sick, etc hours total more than 40 during the week. Any "simple" solution for this would certainly be appreciated.
However, I also have to compute (during the same work week) any compensatory time our non-exempt employees earn each week. Whereas overtime is total hours over 40 that the employee actually works, our employees accrue compensatory time when any combination of work, vacation, sick, etc hours total more than 40 during the week. Any "simple" solution for this would certainly be appreciated. 
Mike,
Do they accrue time off at a regular pay rate, or at a time-and-half (overtime) rate?
The simple solution up front: if you built the spreadsheet from this tech republic article you have an Overtime field, and that would be your hours OT for a pay period. Might need to tweak the scale/calculations to adjust for your employees!
Do they accrue time off at a regular pay rate, or at a time-and-half (overtime) rate?
The simple solution up front: if you built the spreadsheet from this tech republic article you have an Overtime field, and that would be your hours OT for a pay period. Might need to tweak the scale/calculations to adjust for your employees!
They accrue at regular rate and the spreadsheet would have to be configured, if possible, to differentiate between overtime or comp time while providing a weekly, or in our case, monthly total. Way over my head for sure!
We can come up with a formula for you -- but I need all the details. Now, the overtime in this particular sheet is greater than 8 hours worked in any given day, but it only considers actual hours worked. You want anything over 8 hours in a day that also considers sick time and vacation? That one confuses me a bit -- if an employee took 4 hours of sick time and then worked 5 hours, they'd be an overage of 1 hour. Why wouldn't the employee just claim 3 sick hours instead?
Thanks for your response. The key for us is not how many hours an employee works per day, but per week. (Our work week is Sun-Sat.) We are not tied to working an 8 hr day. For exampe, my supervisor grants me the latitude of a flex schedule and I work 8.5 hrs M-T, then 6 hrs on Fri. Even though I work more than 8 hrs per day, my schedule only totals 40 hpw. If I happened to work 7 hrs on Friday, then I would accumulate 1 hr of comp time for the week. It's the same for n/e personnel, except they do get the overtime for hours physically worked over 40 per week. Hope this makes sense.
The example sheet still handles your situation, doesn't it? You just go buy the total at the bottom rather than the daily subtotals. Wouldn't this work?
Another thought I had, just to give you more detail. It is possible for a n/e employee to accrue overtime and comp time in the same week. Example: Monday is a holiday (8 hours) then the employee works 11 hpd Tue-Fri. Total hours for the week would be 52 hours. Of that, 4 would be computed as overtime (x1.5) and 8 would be computed as comp time.
There's no column for holiday pay in the sheet. You could easily add one. Or, you could use what's there, as is and enter holiday hours as vacation. However, I think if an employee gets paid holiday pay, a separate column would be easier to track.
The sheet doesn't deal with compensation, but that would be easy enough to add by adding a row that included rate for each column and totaling them.
I don't think most companies would honor comp/overtime in a holiday week, but you know, they might. That's why the sheet is so simple -- every company has their own policies, but this sheet can be easily enhanced to provide more information.
Excel can do it all for you, if you use the right formulas!
The sheet doesn't deal with compensation, but that would be easy enough to add by adding a row that included rate for each column and totaling them.
I don't think most companies would honor comp/overtime in a holiday week, but you know, they might. That's why the sheet is so simple -- every company has their own policies, but this sheet can be easily enhanced to provide more information.
Excel can do it all for you, if you use the right formulas!
Ours was a little more complicated as it allowed 3 sheets at a time active, when the fourth two week period was opened the first of the three became no longer active, any changes made in it no longer were automaticaly calculated nor added to the annual summary sheet. It was setup for a 26 payperiod year, if we happened to have a 27 payperiod year (once in a great while), then it took more work. The gentleman who had set it up had formulas for everything, in locked hidden cells, that not only provided for calculations but for drop down lists, automatically popluated the days of the week, the payperiod span, the number of hours for overtime, comp time, anyother time not worked...We have since been moved to an Oracle based system that does about the same thing, we also had spreadsheets for our Labor Distribution Codes to help management figure out how their money was being allocated, and for the technicians, it was a way of "charging" their time to a project, the new system is similar, it just standardizes timekeeping across the different divisions, providing a central database accessible from any computer.
The tricky part is entering the time values. In figure H, they are entered as decimal numbers, meaning that 8.5 is actually 8:30. It is easier, up to a point, to enter 8:30, 17:16 or whatever time you like, directly and I concur with the author in using 24 h format as it is easier.
Excel uses a convert the calendar and hour systems to a decimal system. 1 is one day, 0.5, 12 hours and so on. That way it facilitates the calculation.
Try entering several hours as 8:15, 8:20 and so on, and then format the number as general and you will see the decimals.
If an employee works past midnight, you will have to enter the date also. For example: In 1-10-10 22:00, Out 1-11-10 6:00. That way you are always substracting the lesser from the greater number, and you don't get negative numbers.
If you want a speedy way to enter time values, you might consider using one column for the hour, another for the minutes and another to convert. For example; A2=8, B2=30, C2 contains the following formula =A2/24+B2/24/60 and then format as hours.
Excel uses a convert the calendar and hour systems to a decimal system. 1 is one day, 0.5, 12 hours and so on. That way it facilitates the calculation.
Try entering several hours as 8:15, 8:20 and so on, and then format the number as general and you will see the decimals.
If an employee works past midnight, you will have to enter the date also. For example: In 1-10-10 22:00, Out 1-11-10 6:00. That way you are always substracting the lesser from the greater number, and you don't get negative numbers.
If you want a speedy way to enter time values, you might consider using one column for the hour, another for the minutes and another to convert. For example; A2=8, B2=30, C2 contains the following formula =A2/24+B2/24/60 and then format as hours.
That is a good point about employees whose shift or overtime period includes midnight. Such "graveyard shift" workers must enter the date as well as the time. Is there a way to simplify this?
Most Excel useers don't realize that it comes with the programming language VBA (Visual Basic for Applications) which can automate much of this. However, VBA can be complex to learn since many of the features are not intuative.
Most Excel useers don't realize that it comes with the programming language VBA (Visual Basic for Applications) which can automate much of this. However, VBA can be complex to learn since many of the features are not intuative.
I think there's a formula that can handle expanding midnight, without requiring both dates -- I'll see if I can find it.
This timesheet is simple, but I need to add something in formula for not calculating Saturday and Sunday as regular hours.
We use monthly timesheets, sometimes we have to work on Saturday and Sunday (usually Saturdays and Sundays are overtime hours, in rare cases are compensation hours)
My question is how to make the Saturday?s and Sunday?s hours as overtime?
Thanks in advance
Fadiluta
We use monthly timesheets, sometimes we have to work on Saturday and Sunday (usually Saturdays and Sundays are overtime hours, in rare cases are compensation hours)
My question is how to make the Saturday?s and Sunday?s hours as overtime?
Thanks in advance
Fadiluta
You probably don't need a complex formula. First, your overtime hours for Sat and Sun are already there in your subtotals -- any Sat and Sun time is overtime, right? Just sum them. Then, subtracting your Sat and Sun hours from the straight time would compensate there, right?
If I misunderstood your requirements, just let me know.
If I misunderstood your requirements, just let me know.
Thank you for your prompt answer, ssharkins..
?any Sat and Sun time is overtime, right? Just sum them. Then, subtracting your Sat and Sun hours from the straight time?
For the time being I am doing the same, but this has to be done ?manually?, and when I do that it happens that time to time I forget some hours for our employees.
So I believe that having a complex formula that makes hours of Sat and Sun as overtime would saved me (and probably many other, too). I believe that formula has to be something like this
on G7 =if(B7=sat or sun,0,(then the other part of the formula), on H7 =if(G7=0, sum (c7:f7), (than the other part of formula), (I have tried this but I got lost)
Thanks in advance
?any Sat and Sun time is overtime, right? Just sum them. Then, subtracting your Sat and Sun hours from the straight time?
For the time being I am doing the same, but this has to be done ?manually?, and when I do that it happens that time to time I forget some hours for our employees.
So I believe that having a complex formula that makes hours of Sat and Sun as overtime would saved me (and probably many other, too). I believe that formula has to be something like this
on G7 =if(B7=sat or sun,0,(then the other part of the formula), on H7 =if(G7=0, sum (c7:f7), (than the other part of formula), (I have tried this but I got lost)
Thanks in advance
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
