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
Hi, I am creating a timesheet that has needs both daily overtime and overtime after 40 hours worked. So in your timesheet ,for example, works 10 hrs on Monday then that is 8 straight and 2 ot, then he works until Saturday at 8 hours per day, so Saturday should show no straight hours worked, only 8 ot hours. How can I have a formula that calculates daily ot and ot when over 40 hours. I need both as this is common
In our state employees are paid OT for over 8 hours in a day in addition to 40 hours in a week. The simple timesheet would work for me if it factored this in. Anyone have any ideas? I am driving myself crazy trying to figure this one out!
The Swifttime is an electronic web based application making it easy for employees and managers to improve work efficiency. Best suited for firms with remote employees and companies with hourly employees where proper documentation of working hours are required. This application can be accessed from any web based device like mobile devices(Blackberry or iphone). It has following advantages:
? Provides a menu driven user interface.
? High accuracy in payroll management.
? Employees and managers can easily access this application via Internet or mobile devices.
? No start-up cost is required.
? Nightly backups of all data.
? Helps to ensure compliance with federal wage and hour laws.
? Advanced reporting module.
? Integrated billing and invoicing module.
? Data can be exported to Excel spreadsheets for further processing.
? Integration with payroll programs like ADP, Quick Books, Peach tree and others available
Visit : http://www.swifttime.me/
? Provides a menu driven user interface.
? High accuracy in payroll management.
? Employees and managers can easily access this application via Internet or mobile devices.
? No start-up cost is required.
? Nightly backups of all data.
? Helps to ensure compliance with federal wage and hour laws.
? Advanced reporting module.
? Integrated billing and invoicing module.
? Data can be exported to Excel spreadsheets for further processing.
? Integration with payroll programs like ADP, Quick Books, Peach tree and others available
Visit : http://www.swifttime.me/
Our company goes from overtime to double time after 3 hours of overtime. How do I adjust the overtime formula and recreate the double time formula.
All,
Someone on Mr Excel helped me with the formula for working past midnight some time ago
Here is the formula: =ROUND((MOD(G2-D2,1)-MOD(F2-E2,1))*24,1)
Tested for several different scenarios, worked for every test case. You will need to enter all times in Military time (24:00) Ex: 12 midnight = 00:00, 8am = 08:00, 2pm = 14:00 (12+2)
Someone on Mr Excel helped me with the formula for working past midnight some time ago
Here is the formula: =ROUND((MOD(G2-D2,1)-MOD(F2-E2,1))*24,1)
Tested for several different scenarios, worked for every test case. You will need to enter all times in Military time (24:00) Ex: 12 midnight = 00:00, 8am = 08:00, 2pm = 14:00 (12+2)
I think you meant =A7. When i put =A8 into the specified cell the days started on the wrong day the last day didn't come out since it was moving into a blank cell
is there an excel tutorial on how to formulate all my weekly timesheets to be input to a yearly timesheet.
The timesheet I'm working on is for a salaried employee and has hourly breakdown columns of "straight", "holiday" and "maternity leave"...no "overtime" column. If a person works 9 hours in a day, I don't want the extra 1 hour to go to "overtime"...I want it to stay in the "straight" column. But, without translation of the current formulas, I don't know what adjustments to make to the formulas to make them come out right. Can someone help?
What is the best way to run a report from said Timesheets? I am using this recordkeeping. Say I want to run a report for said employee to see the first day they started and the last day they worked.
What is the best way to record. Should I do a timesheet for all weeks or just a continous workbook for each employee?
What is the best way to record. Should I do a timesheet for all weeks or just a continous workbook for each employee?
Great tutorial but a bit long. If you are looking for a quick and easy timesheet calculator you might like: http://www.excel-easy.com/excel-examples/time-sheet.html
Good explanation in manage our time in excel sheet but important to every one
Now days, all humans know about how much time to spend in one project? and how to schedule it to improve. Time Management is very essential to utilize your work and your employ's work..
Now, we started to develop [Time sheet] : http://qualitypointtech.net/timesheetdemo/index.php web application
It is best and efficiency application for monitoring working time and available for employee wise/Project wise/Date wise time report. Just work with our [Time Sheet DEMO VERSION] : http://qualitypointtech.net/timesheetdemo/index.php
More Details about [TIMESHEET Application] : http://qualitypointtech.net/webtimesheet/index.php
Now days, all humans know about how much time to spend in one project? and how to schedule it to improve. Time Management is very essential to utilize your work and your employ's work..
Now, we started to develop [Time sheet] : http://qualitypointtech.net/timesheetdemo/index.php web application
It is best and efficiency application for monitoring working time and available for employee wise/Project wise/Date wise time report. Just work with our [Time Sheet DEMO VERSION] : http://qualitypointtech.net/timesheetdemo/index.php
More Details about [TIMESHEET Application] : http://qualitypointtech.net/webtimesheet/index.php
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
