I have a report which tells me the total time the colleague will be getting paid for and there
clock in and out times.
I need to determine which shift bracket there hours fall into based on the time bands.
David worked 8 hours , started at 10:00 finished at 18:10 , so thats 4 hours recorded in days and 4 in afters since he worked across both shifts. the 10 minutes is not being paid so it doesn't need to be recorded.
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!!
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 have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?
E10 = 19:00 or start of night time hours B3 = start time C3 = end time D3 = day hours workeds =24*IF(E10
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
As per attached spread sheet in the yellow highlighted cells, I am trying to input a formula to automatically calculate the hours between specific times for shift workers so I can easily calculate their penalty rates however I just cant seem to get it to work, the main issue being midnight.
how would I calculate the number of hours in a shift after midnight. What I want to say is that the number of hours after midnight on a friday shift to be taken off from friday total hours and added to a saturday shift.
I would like to find out a way to work out the number of minutes worked during particular shifts for weekdays. Basically I have two columns, one for start time, and one for end time. They are formatted like dd/mm/yyyy hh:mm. So they have the date in there as well.
I would like a formula that would look at a range say A1-A11 and work out what shift it is and then output number of hours worked per shift. Day shift would start at 8am and finish 5pm, Twilight shift would start at 5pm and finish at 9:30pm, night shift would start at 9:30pm and finish at 8am the next day. So I would need it to check for example the start and end times (and dates) and then output 3 rows that show the total minutes worked.
There will be multiple days so it would need to say for example Monday Day, Twi, Night, Tuesday Day, Twi, Nights etc. Up to Friday Day shift because we don't work Friday Twilight or Nights, and we don't work Weekends.
Basically there is a list of jobs completed with Start Time and End Time for each and I also have a column that works out the number of minutes worked on that job. So the formula would need to look at many rows.
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:
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:
…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?
Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that? For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell).
Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )
I'm trying to make a way to track if I've worked more or less than the 39 hours/week I'm paid for. At the end of each week, I have a total of how many hours and minutes that I've worked .
On column C I have what I should work.
On column D I have what I did actually work
On column E I'd like to convert automatically Columd D to minutes for calculation purposes
Column F to know if I worked more or less than what I should've subtracting C and E
Column G to have an ongoing tally to know if I need to work more or less
Column H and I could probably be the same thing. Ideally what I'd like is to have a formula pull the information from column G and put it into workdays, hours, and minutes with 1 workday being 7 hours and 48 minutes.
Since I tend to work too much, I'd like to know if I've worked 3 days too much during 1 month, I can take 3 days off the next month to get everything zeroed back to where I don't owe the company anything and vice versa.
One of my administrative duties is to keep a record of all of the flying-hours completed by a group of twenty pilots. I've constructed a spreadsheet and entered all of their flying records into it.
At the head of each column I have the date, aircraft type, registration number, pilot name, co-pilot name, other crew name, day flying, night flying, solo, dual, total captain hours, etc.
Whilst that I've completed the easy part of this project and that I can transfer each individual pilots flying-hours into his own seperate logbook (by filtering and copy/pasting into another worksheet), there are three other reports that I'm required to provide:
1. To be able to list the number of flying-hours completed during the previous 7 days (for each individual pilot).
2. To be able to list the number of flying-hours completed during the previous 30 days (for each individual pilot).
3. To be able to list the number of flying-hours completed during the previous 90 days (for each individual pilot).
I am trying to work out the minutes elapsed for a call monitoring system. The hours monitored are between 05:30 and 19:00 - so if a call gets logged outside of these hours then the minutes calculated will be calculated from 05:30 the same day if logged on or after midnight or 05:30 the next day if logged before midnight (ie the next 05:30).
I would like to write a macro that can return the number of hours worked during the night shift.
In this case, the night shift starts at 21:00pm and ends at 6:am next day.
The column "I2" returns the number of hours worked during the day - formula ((F3-C3+(F3<C3))-(E3-D3+(E3<C3)))*24); The column "J2" gives the overtime hours taking into account the number of regular hours allowed - IF(OR(I3="",I3<6),"",IF(I3>H3,"",I3-H3)); Cells "C3:F13" allows users to set up (using a data validation list) starting time, lunch in, lunch out, end time ( columns C and D AM; E and F PM);
My question is: How can I calculate night hours in column K, without having conflits with numbers returned in column I (worked hours)? Is it possible to write a macro for this?
Below please see the table:
B C D E F G H I J K
1 Name Start Time Lunch In Lunch Out End time BREAK? Regular hours Worked Hours Overtime Night Shift
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete.
I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays.
here are some examples.
from - 2/12/2004 13:00 (thursday) to - 2/13/2004 9:00 (friday)
answer should be 5 hours
from - 2/13/2004 14:00 (friday) to - 2/16/2004 10:00 (monday)
I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends.
Date 1 Date 2 Result Time 23/07/07 8:00 24/07/07 14:55 15:55
So far I can't get the caluclation that will compensate for the after hours and week ends.
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column. For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours. In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
i didn't realize is that my current funcation that calculates vacation hours... will increase after a new year. i'd rather have it not increase until they are "reset" for the new year. how do i stop the function? =VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2)
basically goes to a lookup table with the caculations. PROBLEM: if a user's anniversery date passes, they may go from a 1-2 year status to 3 year bump... this will auto adjust the amount of vacation hours they have. if the reset button is ran to calculate the vacation hours, it might over calculate giving the user 40+ extra carryover hours. anyway i can make this vlookup stop when the current date is or has passed the anniversery date, yet has not been reset? maybe a count down timer, not sure. http://www.ozgrid.com/News/excel-eva....htm#ExcelTips
Start Shift = 2009/11/10 09:27:06 (GMT-6:0) End Shift Time= 2009/11/10 15:13:03 (GMT-6:0) eg. Total Time = 5.3 hrs
I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.
Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.