I am trying to calculate earnings based upon hours worked based on 24 hours format (time out - time in = time worked) multiplied by hourly rate. I cannot get the proper calculation. How can I attach a copy of the Excel worksheet?
View 9 Repliesi have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am.
As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift.
if work, 9am to 7:30pm, and have break between 2-3pm, should have 1.5hrs shift and 9.5hrs total
if work, 7:45pm-9:45pm, and have break between 8:30-9pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs
if work 3pm to 12am and have break between 7-8pm, total hrs work is 8 and shift hrs s/b 5hrs
How can I get the median of a column that contains both hourly and annual rates? I would like to either multiply the hourly rate * 2080 or divide the annual rate by 2080 then get the median.
View 6 Replies View RelatedAny way to calculate the total hours staff work based on the mininum time of the first transaction to the maximum time of the transactions. I used a DMIN and DMAX function to get those times per employee. The issue is then the time goes over from one day to the next, such as from 11 PM to 4 AM the next day. As you can see in the data below,the fourth record shows the minimum time as 12 AM and the max as 11 PM with total time worked as 23 hours. In this example, the total hours worked should be five hours.
min time
max time
total hours
7:00 AM
16:00
9:00
[Code] .......
I need to create a formula to calculate monthly hours worked (144.20) by hourly rate (£14.25). So far everything ive tried has given me awrong answer. I cant seem to make the total right. From what ive come up with (Not good) the total accepts the hours-just not the minutes.
View 8 Replies View RelatedI'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:
Principal: $120 (user input value)
Duration: 9 (user input value in days, compounding daily)
%Intertest: 4% (user selected value, either 2% or 4%)
Max interest you can earn: $6 (fixed)
Max interest generating money: $150 (variable dependant on %interest, = $150 or $300)
Response/Answer is final value. I don't need the daily results like the example.
Result would be: $170.84
$124.80 (4.80 interest)
$129.79 (4.99)
$134.98 (5.19)
$140.38 (5.40)
$146.00 (5.62)
$151.84 (5.84)
$157.84 (6.00 reached the max interest level)
$163.84 (6.00)
$170.84 (6.00)
my equations I have so far only do one (below 150 total) or the other (above) but not both. and its just a regular formula: =IF(P<M,IF(P*I^D<M+1,P*I^D,"over limit"),P+6*D)........................
I need to expand on this formula.
I require a formula that will break down daily hours worked into rate categories eg Normal Time, Time & Half, Double Time.
eg. Column E = Total time worked
Column F = Normal time
Column G = Time & Half
Column H = Double Time
What I would like to do is enter hours into Column A and a formula in Column B will split of hours to a maximum amount of 7.6hrs then the remainder of the hours be placed in Column C to a maximum of 2 hours and Column D, no maximum.
These are the formula I am currently using
column B
=MIN(E2*1,7.6)
column C
=MAX(MIN(E2-7.6,2),0)
column D
=MAX(E2-9.6,0)
I would now like to be able to split the hours over 3 rows x 3 columns
Example 1
Row A B C D
1 3 3 0 0
2 5 4.6 .4 0
3 2 0 1.6 .4
Example 2
Row A B C D
1 6 6 0 0
2 21.6 0.4 0
3 0 0 0
The total hours in Column B cannot exceed 7.6 hrs
The total hours in Column C cannot exceed 2 hrs
There is not maximum for hours in Column D
For a landscaping company, who is looking to service gardens more effectively/efficiently, they are mirroring the below variables to the amount the client is willing to pay per hour. I also have a sample file.
I have, on Sheet1, 8 columns I'm working with:
R3:R132 is the currently hourly rate
L3:L132, M3:M132, N3:N132 are current units, volume, and labor hours respectively by location.
AD3:AD132, AE3:AE132, AF3:AF132 are new units, volume, and labor hours respectively by location.
AI3:AI132 is the suggested hourly rate by location. Note this is already calculated and filled in. I need to calculate new units, volume, and labor hours based on the suggested hourly rate.
On Sheet2, I have 4 columns I'm working with:
A2:A161, B2:B161, C2:C161, B2:B161 are hourly rates, units, volume, and labor hours respectively.
They are laid out in combination with one another to show possible combinations of these variables that make sense for the landscaping company.
My goal is to figure out what combination the company wants to go with for a given hourly rate on Sheet1 given the following logic to follow:
1) Attempt to reduce labor hours to get to suggested hourly rate - This is to calculate new labor hours on Sheet1 in cells AF3:AF132.
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 Replies View RelatedI am trying to build a costings sheet for shift work. The first problem I am experiencing is getting excel to split up a shift into the different bands.
For example the shift needs to be calculated on different rate bands, from 00:00 to 08:00, 08:00 to 19:00 and then 19:00 to 00:00. There is a different hourly rate for each band. Therefore if I enter a start time and end time, I need excel to calculate the total charge over the different rate bands.
For example a shift 07:00 - 21:00 would have:
1 hour 07:00 - 08:00 at Night rate
11 hours 08:00 to 19:00 at Day Rate
2 hours 19:00 to 21:00 at Night Rate
Once I have this worked out I have the added complication of different rates on Friday evening,s, Saturdays and Sundays etc. But at the moment one problem at a time !
1/1/20080:221/2/200814:051/1/20082:451/3/200819:301/1/20089:001/1/200819:33
In time Out time
I 've used the site many times in the past, but this is my first post.
Above is a small example of the data Im working with. I'm not wanting to plot all of this out by hand, so hopefully someone can help (there are around 30,000 samples). I'm looking to track by hour when a customer is actual here. The first row is one customer in @ 00:22 and out the next day at 14:05.
I would like to have dates of the year down column A and hours 0 through 23 across row 1. Then a number of customer here on 1/1/2008 by every hour of the day on to 1/2/2008 so on and so.
I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!
For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50
Start time: 12:30
Finish time: 23:00
Hours worked: 10.50
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 A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.
If I were to do it manually 365 times, the macro would look like this:
Copy Row A1:X1
select sheet 2
Paste Special > Transpose (fills A1:A24)
repeat for rows 2-365
Tried using this from a previous post, but it takes A1:A24 then B1:B24 and so on and makes them a single column - so it doesn't work unfortunately.
Sub MakeColumn()
Dim rng As Range
Dim LastRowSrc As Long
Dim LastRowDst As Long
Set rng = Worksheets("Sheet1").Range("A1")
[Code] .....
I have this spreadsheet and in it the time is changed from military time to regular and then I use a formula to calculate hours worked. On some of these the total is off by one minute. Does anyone know how to fix this?
I don't know how to paste the spreadsheet so you can see formulas,
I need to develop a work sheet for agency booked people to count the number of hours worked by them on daily basis. Agency can clock in at three different times and clock out at 6 different times. i tried but could not even develop logic to calculate the total earned hours. i attached the sheet for reference.
View 5 Replies View RelatedI want to calculate the subscription rate as follows:- Subscription is fixed at the minimum rate of Rs.200 and is incremented at the rate of Rs.10/- for every Rs.200/- thereafter?
View 2 Replies View RelatedAttached sheet, I am struggling with the formula that will add up the hours overtime worked per day when I enter start and finish times.
Standard working hours are :
mon to thurs 8 hrs per day 8m to 16.30 (with 30 mins unpaid break)
Friday 6 hrs per day
Saturday all hours are overtime
Hours Commited sheet.xlsx‎
i m trying to work out the productivity of employees based on how many hours they work (Time in Back Office). How many pieces of work they complete(Back Office items Completed) if 1 piece of work should take 7 mins. the item in red is what i cant seem to figure out.
View 5 Replies View Relatedto calculate how long a ticket is open in our system before being resolved. I don't want to count weekends, and if the ticket is 'suspended', I don't want to count that either. There is also the factor that the ticket 'un-suspend' date may be later than the ticket 'closed' date. Which is the bit that's throwing me.
So, I have the following fields
Ticket Open, Ticket Closed, Ticket Suspended Date, Ticket Unsuspended Date
A sample ticket might be (using above fields)
02/11/09 09/11/09 04/11/09 30/11/09
That 'should' equal two days (16 hours) as the Unsuspend date falls after the close date so it was suspended from the 4th until closure.
Now I want to know, in hours (8 hour day) how long that ticket took to resolve (i.e close), remembering you can't count the time it was suspended, or any time that fell over a weekend. Also not all tickets are suspended.
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example
Hours ST OT Total Hrs Gross Pay
8 2 10 ?
i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
I got a problem with a time calculation, if it's even possible.
I want to calculate the amount of hours that have been woork in a slot.
Example:
First slot is from 08:00am till 17:00pm, time cost 100%
Second slot is from 1700am till 24:00, time cost 110%
Third slot is from 24:00 till 08:00 am, time cost 120%
I'm working from 08:00am till next day 01:00am
So my hours are:
9 in slot 1,
7 in slot 2,
1 in slot 3.
Is this possible to calculate in Excel?
I got a file attached with a sort of layout.
Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK. I have used this since but find my calculations never work out the same as my BS, although to my advantage! It is =B3*B4/360*DAYS360(B5,B6,TRUE) Where:
B3=Capital
B4=Interest Rate
B5=Starting Date
B6=Finishing Date
For some reason the formulae uses 360/year and not 365/year. Using both still gives wrong answer.
I am trying to calculate a new base pay rate, but I need it make sure it is at least brought up the new minimum and capped at the new max of the range where applicable. The increase is based on 10%
So here are the columns used:
L = Current Base Pay
Q = New Min of the range
S = New Max of the range
U = where I want to calculate a 10% increase of L, but ensuring it is brought up to at least the min (Q) or not over Max (S). In other words if my min is $12 and Max $18 and my new base pay is $16 -- then I am good. However it if is $11.50 I need the formula to return at least $12. And visa versa -- if the new rate would be $18.50, I need it to return no more than $18.
I am trying to calculate APR (Annual Percentage Rate) for a mortgage loan that has a balloon feature. I have tried to the the RATE function but it only gives me the APR for a loan that is ammortized over 30 yrs and paid in 30 years. I need the APR for a loan that is ammortized for 30 years with a baloon in 5 years.
View 9 Replies View RelatedI work different length shift some of which are over-night. I'm using the formula
=IF(((C1-B1)*24)<0,(C1-B1)*24+24,(C1-B1)*24)=IF(((C1-B1)*24)<0,(C1-B1)*24+24,(C1-B1)*24)
Where cell "B" is start time and cell "C" is finish time. This calculates hours worked whatever shift I'm on.
Is there anyway I can also deduct a hours rest break if I work more than 7 hours ?
I am working on a sheet that will allow a user to enter a start month (from a pre-defigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter.
It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;-) I have thought of several options, but none of them have worked 100%.
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View 19 Replies View RelatedIt's been several years sine I had to look at calculating amount of time worked.
Can you please look at this old spreadsheet of mine and verify that the formula is correct?
It appears to be ok to me, but I don't want any errors when it comes to paying my employees
Formula: ...
I am looking to reassess the useful life of existing fixed assets by one/two years.
For example:-
Original cost $1,727.00
Start Date 30/12/2008
Current End of life 29/12/2013
New End of Life date 29/12/2014
5 year - 20%
NBV as at 30/06/2013 $174.12
How can I calculate the new rate, as I can not change the prior financial years depreciation. I am looking for a calculation, to test the assumptions, and see the overall change. This is just one example.