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).
Im using xl2003.
I'm simply trying to divide column A by column B in column C. However in my field of data A and B are sometimes zero values that return a #DIV/0! answer in C.
Without introducing and more columns how can i eliminate this error value?
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.
I need to calculate below hours
07:50
07:50
07:50
07:50
07:50
The answer suppors to be 37:30 Hours but its showing total diffrent value. i used sum(E1:E5) Excel formula, but its not working.
I am trying to create a spreadsheet that auto calculates my emp. time.
However I do not want to use military time. I can get it to work by =a2-a1 but only if it is 8.5 and 17.5. Any ideas how I can do clock in 8:30 clock out 4:30 = 8 hours?
I am using the following formula to calculate business hours.
=(NETWORKDAYS(R9,T9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(R9,T9),MEDIAN(MOD(T9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(R9,T9)*MOD(R9,1),"17:00","08:00")
The business hours considered here is 8AM - 5PM, Start time in R9 and End time in T9. Now the problem is its calculating the correct value when the days are same, for e.g.,
Condition 1
When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00"
Condition 2
While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".
I have enclosed a sheet with the dilemma i currently face.
Ive tried multiple variations on a solution none of which have been 100% accurate.
Basically the work day is split into 3 shifts :
Days ( 06:00 - 14:00 )
Afters (14:00 - 22:00 )
Nights ( 22:00 - 06:00 )
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.
Ie :
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.
the sheet should explain things better.
I am trying to figure out the calculation of cycle time from my worksheet. I have 1419 hrs equals 59 days 03 hrs.
If A2=1419
I tried this formula
=INT(A2/24)&" days " &MOD(A2,24)&" hrs"
Which gave me an answer of 59 days 3 hrs.
I was wondering how they got "03 hrs"?
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 looking for a funtion that will help me add the hours someone is scheduled to work. The problem I am having is the format. Here's the format: The days of the week are Mon - Sun (D3-J3). L3 is a total of the hours they are scheduled to work for the week. Cell D3 is 8-2. Cell E3 is 9-3, etc. for the rest of the week. What I am looking to see if possible is in L3 take the row, Take D3 at 6 hours, E3 at 6 hours, etc for the rest of the row, and add them together for the total hours in L3. I have attached a small example file of what I'm trying to do. The format I am looking to use for the hours is as described above.
View 11 Replies View RelatedJust like if I put the employee's number and work time for each day,
The excel will accumulate the hours automatically somewhere in the sheet. (I don't want the total hour cover each day's work time)
Is there anyway I can do it?
I am working on a monthly employee work schedule that has 2 stores. All employees work at both stores and the boss wants 2 separate schedules.
I have attached what I have started, which at the moment I am stuck.
I would like to have the Row 5 total hours to always reflect (subtract) a 30 minute lunch. I know it would be easier to have another column, but the boss wants it this way,
I also need the total hours in column H to reflect the total of each day (with the subtracted lunch break). In this column I am having the figures turn red if over 40 hours and green if it under 40 hours.
With having 2 stores with the employees working at both, I need to somehow have the hours of the employees add up from one store to the other (a running total, if you will).
Lastly, if I have an employee not scheduled for a day or need to put other data such as, VAC, HOL or blank cell etc in a cell, how can I get this to work and not have the formula go nuts when it is not in a time format.
I charge £45 per hour with a couple of conditions - minimum 8hr day and then 1.5T after 12hrs.
Is it possible to have a table where I put in my hours and then Excel tells me how much I should charge?
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
[Code] ......
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)
answer should be 5 hours
I can't figure out how to do a proper formula for calculating time. For instance I have one column that says "Time In", the next is "Time Out" and the other is "Lunch Time". My calculation needs to be this:
Find the total hours between the Time In and Time Out and then Minus the lunch time to get total hours worked for the day. The only way I can get this to work is using 24 Hour time format. Is there another way?
I have to calculate employees work hours for overtime.
in the timesheets:
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 5:00 Regular <----- to make 40
5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime
How can I do this?
I want to make a command button for macro to perform this.
Want to work out formula for working hours If Mr Smith is booked to work I must pay him a min of 8 hours pay at rate A then upto 10 hours still on rate A but after that he gets rate B I need to enter his data on a daily spreadsheet
View 9 Replies View RelatedI am currently trying to create a rota / rosta for work which will show people what hours they are required to work, in a format similar to below:
NAME | SAT | SUN | MON | TUE |etc >
Person A| 8:00 - 17:00 | 13:00 - 22:00 | OFF | 8:00 - 13:00|
Person B| OFF | 17:00 - 22:00 | 8:00 - 17:00| OFF |
This is simple enough to create but I also need Excel to calculate how many hours each person is working in a week and the total number of hours used per day and per week. I found out that I can do this using a formula to work out hours and minutes between two times. However what I would also like Excel to do is deduct the unpaid breaks which the person is entitled to, however this depends on the times that they work, i.e. in the example above Person A would be entitled to a 1 hour unpaid lunch on Saturday, a half hour unpaid tea on Sunday and no unpaid break on Tuesday. Therefore 1 hour would be deducted off the Saturday total hours, 30 minutes would be deducted from the Sunday total hours and nothing taken off the Tuesday.
I am creating a 'HelpDesk Issue Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days and hours are between the two days to give me a TURE 'Response Time.'
I have been to several sites and forums looking for the answer, but I have not been able to find a solutions that works for me. Please find attached a 'stripped' down version of my project.
Variables:
- 'Date Received' (H11)
- 'Date Actioned.' (I11)
-'Response Time' (K11)
Constants:
Work Days = Monday to Friday
Work Hrs = 8:30 AM to 5:30 PM (no lunch break)
Public Holidays = (AC13:AC30)
Formats:
Date Received: dd/mm/yyyy hh:mm AM/PM
Date Actioned: dd/mm/yyyy hh:mm AM/PM
Response Time: d - hh:mm
If there is someone out there wiling to put me out of my missery with this one, you will have a friend for life.
I want to calculate the time in hours between to dates excluding non-working hours and sundays and holidays.
My office timings: 10:00am to 05:30pm Saturday is working day. Only sundays and holidays to be excluded.
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.
View 3 Replies View RelatedFor the past month now, I'm trying to calculate some work hours (night hours actually), based on a reference.
Let me show you the table:
- Column A has all of the schedules of the employees
- Column B has the numbers of hours for each schedule (all have 8.5 hours/day)
- Column C has the reference for the night hours.
Now what I'm trying to do, is to find a formula that will calculate the numbers of the night hours using the reference in Column C
Row 23 in that table has an example of what I'm looking for.
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 RelatedWhat 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.
eg.
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 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.
It is data for a time study to compute labor hours. I have managed to group everything together per "work order" and "Employee ID". I am trying to sum up the labor hours by work order. I know it sounds simple but I do have another question. Once the total hours are set. Are they still available in the same format when put in a Pivot Table?
View 2 Replies View RelatedI am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent.
We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2)
My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours?
Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00
If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2
I need to enter a formula that calculates the time a report is received from the time it was recorded in our database. therefore, it needs to exclude non working hours. here are the fields:
A1 2008/10/10 16:30
B1 2008/10/11 09:30
C1 8:00
D1 17:00
A1 = report received
B1 = reported recorded in db
C1 = work day start time
D1 work day end time
Where the answer should = 2 hours.
I am not an experienced excel user and so far the only formula I have now is: =TEXT(B1-A1, "d:hh:mm")
And how do I account for weekends?
I need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):
The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.
Ex1:
Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
will be equal to:
Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
Ex2:
Startdate 06-03-2013 12:01
End date 06-03-2013 14:28
Time Difference 01:29