I am trying to calculate over time based on shift time.
For example: Regular shifts are between 7:00 AM to 3:00 PM (Monday thru Friday). Anything between those hours and on those days should be considered REGULAR TIME. Anything between 12:00 AM to 6:59 AM or between 3:01 PM to 11:59 PM should be calculated as OVERTIME.
Anything on Saturday or Sunday should be calculated as OVERTIME as well.
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).
View 4 Replies View RelatedI want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.
Start/Finish
21/12/09 07:00 to21/12/09 11:09
21/12/09 07:46 to21/12/09 14:41
21/12/09 12:13 to21/12/09 22:08
21/12/09 16:40 to21/12/09 18:05
21/12/09 19:40 to22/12/09 02:34
21/12/09 23:20 to22/12/09 04:39
22/12/09 02:06 to22/12/09 06:15
I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.
All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.
The allowance is paid for all hours worked between 18:00 and 06:00.After 10 hours, a shift becomes overtime and no shift allowance is paid.Shifts are regularly worked overnight (i.e. past midnight into the following morning)
Example scenarios include:03:00 - 15:00 would pay 3 hours of shift allowance09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)03:00 - 21:00 would pay 3 hours19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)22:00 to 10:00 would pay 8 hours
And so on.
I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:
[Code] ........
The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.
The larger formula now looks like this:
[Code] ......
…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.
I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.
Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.
Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?
If I use =now() in a cell it will enter the current date and time. For shift workers, this does not work.
I'm tring to figure out a formula that will only change the date at 6:00am every day. This way a person working the night shift will not see a change in the date on his sheet at all, only the day worker.
I'm looking to calculate OT wages when they happen vs only at the End of Week totals. ie... if the employee hits 40 hours midshift on a Wed, I want to calculate what the total dollars would be for Wed.... a few hours at regular time plus what ever hours above 40 at time and a half.
View 9 Replies View RelatedWe are guaranteed (right now) a 10 hour day. (we are on 4 - 10 hour shifts). So if we work 9 hours say on Monday, we get 9 hours of straight time and an hour of short work week (approx 80% of pay). Now if we work 11 hours on Tuesday (which they can do unfortunately) I get 11 hours of straight time and no overtime. We have to make up for the short work week hour. So a less complicated explanation would be if I dont work more than 40 hours per week, no overtime no matter what I actually worked per day. Seems pretty simple but what I want and need to do is to calculate it per day.
Mon 10 Hours
Tues 11 Hours
Wed 9.5 Hours
Thurs 10 hours
I should get:
Mon 0 overtime
Tues .5 overtime
Wed 0 overtime
Thurs 0 overtime
I got it to the point where If the day where we get short work week is first and overtime after that, it works. But if we work overtime first then get short work week later in the week, it wont calculate it. I know why it wont work now but I dont know how to make it work. LOL Here's a link to the file.
How to report Overtime in a pivot table. Apparently this is more difficult than it seems. Please take a look at the cross posting at [URL]....
I have a sample file there
I have 3 basic job categories... each of those categories start overtime at a different hour.
So, if employee A is a dock worker, he starts overtime at 25 hours. If employee A is an office worker, he starts overtime at 40 hours and if he is a driver, he starts overtime at 55 hours. So based on that info, I'm wanting my spreadsheet to figure out how many hours each employee has left for the week.
The 2nd part question is how many hours per day is left for the week. Rather than making a separate tab for each day of the week, I'd rather the spreadsheet know what day of the week it is and divide accordingly.
Example: In cells A1:A10 random number between 0 & say 20, need to sum ABOVE 8 = (calculating overtime hours)
E.g.
A1 = 0
A2 = 8
A3 = 8
A4 = 10 (giving 2)
A5 = 12 (giving 4)
A6 = 5
A7 = 13.5 (giving 5.5)
A8 = 8
A9 = 0
A10 = 16 (giving 8)
A11 = (Total overtime above 8 hours) 2+4+5.5+8 = 19.5
Need to be able to increase rows and drag across.
I thought I had everything worked out with this timesheet but I've discovered one more problem.
Weekly Timesheet.xlsx
The total overtime hours needs to show that anything over 40 hours in the Total Regular Hours cell is overtime. And it also needs to show only up to 40 hours in the Total Regular Hours cell.
=IF(C5>40,((C5-40)*D5*1.5)+40*D5,IF(C5
View 9 Replies View RelatedI need a formula that will calculate the number of days (ex from 10/1/08-1/1/12) and will differentiate between leap year and a regular year. I am trying to calculate interest between 2 dates and leap year is calculated on a different interest rate. Right now I have it set up so I have to manually type in the leap years in a separate field instead of being able to just you point a to point b.
View 2 Replies View RelatedI need to worked out Hours worked in a timesheet. This was the easy part, the hard part is the clause tha HR threw in, which is:
If you have worked and 8 hour day WITH 1 hr lunch then you qualify for overtime.
if you work a 8 hr day and work through your lunch (1 hour) (so equivelant to 9 hrs) you still do NOT qualify for overtime there for Overtime = 0.
This is cause some people work though their lunch to get overtime, but legally they have to have a break so we are not paying overtime for it. I have basically tried in a formula to replicate this but it works with some data and not with all.
attached is an example, as you'll see the formula works in some cells, but not others.
I know the title is a bit vague, but I cant think how else to word it!
I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target.
I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
I have employees hours worked entered in multiple rows. Columns are Date, Name, Hours worked; then a column that calculates the week number.
The data is entered each day so the Names are not in order and I'd rather not have to resort by name each week.
I'm collecting that data along with several other fields in a pivot table, then I have a summary sheet that uses GETPIVOTDATA formulas to compile a ton of statistics.
I want to be able to filter the pivot table on any given number of weeks. I need to be able to calculate overtime (greater than 40 hours in a week) for each name over the filtered weeks. So I could want to see OT for weeks 4, 5, 7 combined.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
I need to convert regular time to decimal time. example regular time in A1
is 1:38 I need that number to be converted to decimal time = to 13:63. add
12 to the hour and divide 38 into 60 to get the minutes. Our time clock
prints on a 12 hour basis and I enter to excel in decimal time.
I need a bit of a help here. What I'm looking for is basically a formula to calculate shift loading differences. For ex.: Total hrs worked 86. As a part timer, my ordinary hrs are 76. Whatever is over 76 comes as double time ("F").
A B C D E F
87 76 42.5 15 8 11
Thats how it looks initially. "F" is the double time hrs paid (the difference between Ordinary hrs and total hrs worked). Which means that I need a formula to automatically calculate my 20% ("C"), 50% ("D") and 100% ("E") loadings. Correct would be like this: A=87, B=76, C=42.5, D12 and E=0. So, basically this 11 hrs straight double time has to come off the 100% ("E") first and then move on to 50% ("D") and 20% ("C") if necessary. So, need a formula to fields C, D and E.
I am trying to write what I believe is a fairly simple set of calculations to determine non-qualified stock option values. Before this digresses into a discussion of the merits of Black-Sholes methodology, I must tell you that this is a "101" type sheet. I'm not an economist.
The deal is that my sister (for whom I'm constructing this worksheet), gets employer stock options which vest at different intervals.
In the past year, she's gotten some options that vest in two, four, and five year increments. I'm using Excel 2002, with the analysis toolpak, and stock pricing add-in from MS.
Have today's date, date of option grant, and can easily compute time (in years) until options are fully vested.
The trick is that options with a 2-year vesting period are 50% excerciseable in one year, 100% in two.
Four-year options are excerciseable 25% per year for 4 years; 5-year are 20% for 5 years, etc.
I'm trying to write a single date-driven formula to take each grant (individually), and divide it by the appropriate divisor (based on the current date), to figure out the current value.
I can't seem to quite figure it out how to write a formula which can handle:
Shares Strike Price Date of Grant Years Until Vested Value
100 26.57 1/4/2003 5
245 45.67 2/6/2001 4
Value should equal =
(if today's price is grater than Strike Price)
(Current Price * (Shares * (% vested which is based on "date of grant" + "years until vested")) )
-
(Strike Price * Shares * (% vested which is based on "date of grant" + "years until vested") )
how the math to make the "% vested multiplier" work...
Weekly Timesheet.xlsx
This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome.
I'm trying to calculate the hours worked for both my day shift and my night shift.
Day shift (thanks to search ) I have managed to figure out and worked quite well.
=ROUND((E7-D7)*96,0)/4
It totals adds up the time and converts it into a decimal of hours worked.
For example Joes starts at 1100 and finishes at 1330 it returns a total of 2.5 hours worked.
However I strike a problem with nightshift.
They start in the late afternnon and work thoguh into the am.
I have used the same formula but it doesn't seem to work:
=ROUND((K7-L7)*96,0)/4
I assume because once the clock strikes 12 it's a new day and it can't work out the maths.
Lets use the example form about but make it pm.
Joe starts at 2300 and finishes at 0130 it should give me a total of 2.5 hours instead it gives me 21.5 hours
I'm currently working on a rota for which I would like the worksheet to automatically tell me how many staff members I have beginning their shift before 9am and those finishing after 7pm. I currently have this working via a very crude set of IF statements for each staff member for each day of the week, returning 1 if true and 0 if false. Then I have a sum statement at the bottom of each day. Is there a much tidier and simpler way for me to calculate this?
I've attached the worksheet. A quick note is in Q52.
I am conducting some analysis on alarm data of certain devices and am looking for creating a VBA algorithm that will do the following (refer to the table below for example data)
I want identify an asset in alarm (Column F) and based on its asset ID (Column C) and Fault Description (Column D) then find the row when the asset with the same asset ID and fault description returns to an 'ok' Asset State. After the row where the same asset ID and Fault description is found where the asset state returns to 'ok', I want to calculate the total alarm time based on the difference between when the asset ID and Fault description goes from 'Alarm' State to 'OK' state from DateandTime Column.
I want the result to be populated in a new worksheet that displays data with the following column headings as a result of the above calculations: Date, Asset ID, Asset Description, Fault Description, Alarm Time
I have added an additional column to show the type of output I am after.
There are up to 20,000 rows of data I want to perform the above calculation on.
A
B
C
D
[Code]....
If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.
It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.
A2=04:00
A1=23:00
Using (A2-A1)*24 give me -19.00 hours
My SS macro has a line:
s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked
Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?
My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.
View 9 Replies View RelatedHow can I total overtime based on a "Reason Code" and whether the employee chose "Cash" or "Time" compensation?
View 2 Replies View RelatedI need to do is calculate the hours between two clock times minus 30 minutes.
Example:
Cell A1 - 4:00am
Cell B1 - 16:30
I need Cell C1 to say 12 (hours)
Also:
Cell A2 - 17:30
Cell B2 - 02:00am
I need cell c2 to say 8 (hours)
I have tortured the cells into producing the output I want...
Namely time(s) in and out, with the hourly totals - subtracting 1/2 hour
for shifts over 6 hours.
I however have a couple of questions about glitches the approach I am using give me.... I will outline as best I can: wish I could upload a sample...
1) the user inputs (in the time format) the starting and ending times for the individual ie. 9 A = 9:00 AM ... 1:30 P = 1:30 PM simple
2) after the user inputs the total formula for each day is calculated:
=IF(D3="","",(IF((D3-C3)>.25,((D3-C3)-0.020833),D3-C3)))
That leaves the cell null if there is no time in the day...
If there is time, checks to see if more than 6 hours...
If so, deducts 1/2 hour, else does the difference calculation
This does work, but is it the elegant or simple way? recommend?
3) At the bottom of each day totals are calculated by this :
=Text((SUM(E5:E36)),"[H]:mm")
This also works to sum the individual totals to the bottom
But is this the best way to work with the times?
4) the individual then inputs the Actual Hours worked by the team...
And the sheet does a variance calculation between actual hours
Worked vs. Scheduled hours...
=TEXT((D38-D39),"[H]:mm")
Now this is a problem...cannot do negative times....need help!
5) Am having a problem with totaling the individuals weekly total using ...
=TEXT((E3+I3),"[H]:mm")
I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
Excel 2003
H
I
O
P
Q
R
S
1
Start Work
Time Out
Day Count
[code].....