Excel 2007 :: Calculating Man Hours Committed To Breakdowns
May 7, 2014
I'm creating an engineering report and were trying to capture the number of man hrs we are committing to breakdowns the section I'm interested is shown below:-
CraftJob Start TimeJob Finish Time
PL RD02.45 03.15
There are two personnel (shown as initials) in the craft cell with the start and finish times of the job. Can I determine the number of personnel by counting the sets of initials i.e. PL + RD =2 then multiply that number by the time worked in this case 30mins (this will vary) so time committed would be 2*30=60mins.
Excel 2007
View 14 Replies
ADVERTISEMENT
Aug 11, 2010
I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.
View 3 Replies
View Related
Mar 12, 2013
(Excel 2007). I want to separate night hours 22:00-6:00 fom day hours 6:00-22:00 in my work plan.
I am using all kind of shifts for example
8:00-23:00 = 14 day hours 1 night hour
21:00-7:00= 2 day hours 8 night hours.
16:00-2:00=6 day hours 2 night hours
View 6 Replies
View Related
Jul 20, 2012
I want to calulate time by decimal the problem is anything after 1 am wont work
example start at 12:25 finsh at 1:45 time should show up as 1.33
using excel 2007
View 3 Replies
View Related
Sep 10, 2013
I am 2007 Excel user.
I am attempting to take a large data download of time in hours and minutes and average or get the sum. Each cell contains hours and minutes in this format:
203:30 (meaning 203 hours and 30 minutes)
196:05
72:22
6:55
I have formatted the column in everyway I can think of from custom hh:mm to time, to scientific--I have grasped at each straw I have used the TRIM function to ensure there is no leading space. When I attempt to average I receive the #DIV/0! error, and when I attempt to use SUM, I get a dash (-).
I have been researching this on the internet, and have tried everything that is slightly applicable, with no luck yet. I am wondering if it is because the hours are more than 24, so the hh:mm format does not truly apply...
View 7 Replies
View Related
Jul 3, 2012
Using Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.
- Calulation set to auto in Excel Options.
- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.
- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.
View 2 Replies
View Related
Aug 11, 2012
I am using excel 2007. I am trying to calculate the internal rate of return, without creating an array. I figured out that I can use the rate function to give me the same answer as the IRR function if I have one payment stream of say 36 months @ 1000. Where I am an running into trouble is if I have second payment stream of 6 months extension of 1500. Rate # pay 36 amt 75 PV = 5000 FV 500 , How to handle the the last 6 months ?
This is the situation
asset costs is 5000
monthly lease is 75 for 36 months
extension for 6 months is 125
end of lease buyout is 500
what is the internal rate of return without creating an array.
View 4 Replies
View Related
Jun 13, 2014
I want to set up an Excel spread sheet where I can put my total number of sales appointments for the month and the percentage of those I closed. I've done this before, but for the life of me I can't remember the formula I used & I know it's a very simple one! I'm using Office 2007.
View 1 Replies
View Related
May 12, 2009
i have an excel 2007 spreadsheet where i keep baseball stats. On the pitching side have a column for the pitchers innings pitch. The data kooks like this:
1.0
1.1
1.2
where the numbers after decimal points represents thirds of an inning.
In my spreadsheet i have 4 workheets of different divisions that have a breakdown of each team the team i am keeping stats for plays.
i manually enter the innings pitched in the totals for the season as i cannot figure out how to add these numbers from the individual worksheets to calculate the right number. once the number after the decimal point is greater than two than another full inning is recorded not a continuation of the number.
Example pitcher has already pitched 2.2 innings and he pitches 1.2 innings today making a total of 3.4 which should really be 3.1 one last one pitcher has pitched .2 innings and pitches .1 today making a total of .3 innings pitched but it should be 1.0 innings pitched. proper formula to sum the correct totals in the totals worksheet. i am at a loss and ihave been working on this problem for a little while with no luck.
View 5 Replies
View Related
Apr 12, 2014
I'm trying to convert the values entered by users into specific cells into a specific percentage. When I use this macro in Excel 2010, I can enter the value to be calculated directly into the cell and the macro runs automatically. When I open the file in Excel 2007, the macro does not calculate properly unless the value to be calculated is entered into the formula bar.
For example, in Excel 2007, if I enter '30' in cell E11, it should come out as '20%'. Instead it comes out as '0.2%' unless I enter '30' into the formula bar, which then calculates properly. It is far more efficient to be able to enter the value directly into the cell.
[Code] .....
View 2 Replies
View Related
Jun 19, 2013
I have data sheet that has a field minutes. I a pivot table I have the Average Minutes per category. How it the pivot table can I show the Average minutes as Days, Hrs, Minutes? I am working in Excel 2007.
View 1 Replies
View Related
Apr 22, 2014
I am trying to set up a time sheet for staff where it calculates hours worked in and outside of core hours. I can do a calculation to work out what they have worked in and out of core hours providing that part of their time starts of finishes in core times. The problem I am having is when some one works only outside core hours. Our core hours are 6am to 6pm. The problem is when they work from say 7pm to 1am. This is irregular work paid as overtime not shift work.
View 5 Replies
View Related
Mar 13, 2008
I am working in call center, and I get tickets and i have to meet my Services Level Agreement (SLA) with in pre defined working hours.
My working hours are 8:00 AM to 17:00 PM, Monday to Friday. (this includes 1 hour of Lunch time which is not considered as business hour)
As per the SLA, i have to complete the ticket in 40 business hours, i.e 5 business days.
Suppose a ticket is created on 3/5/2008 10:00 AM. I want to know how much time is left for me to work on that particular ticket.
View 12 Replies
View Related
Jan 12, 2010
I want to calculate employee rostered hours or days off from an exported crystal reports. The problem isn't how to calculate the hours but to calculate accurately when formatting changes occur in the exported report. This is an example for the exported sheet data.
http://farm3.static.flickr.com/2748/...50a38e59_o.jpg
So to calculate data intially using the formula
View 10 Replies
View Related
Oct 3, 2008
I have a ROW of numbers (hours) and I need to figure the hours left. Here is an example of 6 colums, Zeros and blanks are valid entries.
C1 C2 C3 C4 C5 C6 Hours Left
16 8 0 0 0 0
16 8 4 4
8 2 1 0 0
16 0 0 8 4 2 2
8 16 10 8 8
I'm trying to get a formula that will figure out hours left. I've tried a number of times.
View 5 Replies
View Related
Dec 12, 2008
I have a spreadsheet where I record individual staff start time, end times and the length of their break. From this I caculate the hours they worked for the day.
E holds Start, F holds End and G holds Break length. Thus using the following forumula gives me the number of hours and minutes:
View 4 Replies
View Related
Jan 1, 1970
I can do the timesheet formula for adding the hours worked as follows:
Start Finish Total
08:45 17:15 8.5
However, it doesn't work when I fill in a whole week work of hours in this format:
Start Finish Total
08:45 17:15 08:30
It works on a daily basis, but when total hours exceeds 24, the formula get's all mixed up - how to I format the total column to account for every 5 minutes worked, which you can't do when converting to decimal??
View 14 Replies
View Related
Apr 23, 2008
I use this formula at work to calculate business hours from Mon-Fri:
=IF(OR(L10="",L11=""),"",(NETWORKDAYS(L10,L11,$N$2:$N$23)-1)*($Q$3-$Q$2)+IF(OR(ISNUMBER(MATCH(INT(L11),$N$2:$N$23,0)),WEEKDAY(L11,2)>5),$Q$3,MEDIAN(MOD(L11,1),$Q$3,$Q$2))-IF(OR(ISNUMBER(MATCH(INT(L10),$N$2:$N$23,0)),WEEKDAY(L10,2)>5),$Q$2,MEDIAN(MOD(L10,1),$Q$3,$Q$2)))
where Q3= business start time 8.30am
where Q2= business end time 5.30pm
thus the difference between 18-Apr-08 16:30 and 21-Apr-08 13:30 is 6 hours.
I now need to adapt this formula for another Department that also works on Saturday from 8.30am to 5.30pm.
View 9 Replies
View Related
Jun 3, 2008
This may seem like an odd request... I recently received a protected worksheet that I entered my date of birth into, and it told me how long (if I pressed F9) I had been alive in y/m/w/d/m/s etc. I was wanting to do a similar spreadsheet to show how long I had stopped smoking for, and a running total of how much I was saving, but I couldn't look at the spreadsheet for help. The stop time was 01/06/08 - 05:00am GMT, up until present, and buying cigarettes I was spending £0.00004166666 per second. Is it possible to create such formulas?
View 9 Replies
View Related
Feb 20, 2014
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.
View 3 Replies
View Related
Mar 18, 2014
I'm trying to calculate shift working hours without using dates.
The scenario is
Cell A1 = Start Time
Cell A2 = Start Time
Cell A3 = Break Time
Basically I want the output to calculate hours worked between:-
0000 and 0600 as a total in cell A4
0600 and 1800 as a total in cell A5
1800 and 0000 as a total in cell A6
Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.
View 5 Replies
View Related
May 2, 2008
I need to calculate time taken to fix a piece of equipment.
A1 B1
BREAK TIME FIX TIME
4/22/08 23:00 4/23/08 04:00
Should be 5 hours, but i can't find the formula to make it work.
View 14 Replies
View Related
Nov 27, 2012
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.
View 10 Replies
View Related
Nov 30, 2012
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.
View 8 Replies
View Related
May 31, 2014
Im doing a spreed sheet to calculate employees hours. I have the employees names in column R and their hours in column S. example R5 = Mike S5 = 8. I need to search column R for all the mike's and total his hours, then move to the next employee and so on... I have already sorted the employees names so that all the names and hours correspond and starting in column R5 and S5 until the proceeding row is blank and then paste the results in column B5 (employee name) and D5(total hours)
View 4 Replies
View Related
Aug 15, 2010
how can i calcaulate working hours between two days.
Working Hours 07:30 - 14:30
e.g. mm/dd/yyyy
start time 01/04/2010 17:34:58
stop time 01/05/2010 08:23:35
View 9 Replies
View Related
Mar 29, 2012
I am trying to calculate when data is out of hours based on it being before 08:00 and after 16:30, plus also weekends.
I have used the following formula to calculate the day of the week.
=TEXT(WEEKDAY(AE2), "dddd")
I have this in a separate column.
This is how my sheet looks.
Actual Start TimeOut of HoursActual Start DateDay of Week
14:1525/12/2010Saturday11:0028/12/2010Tuesday22:3028/12/2010Tuesday
13:3029/12/2010Wednesday09:4030/12/2010Thursday13:0002/01/2011Sunday
09:3806/01/2011Thursday08:0507/01/2011Friday12:0009/01/2011Sunday
11:1010/01/2011Monday08:1512/01/2011Wednesday10:1113/01/2011Thursday
11:1514/01/2011Friday15:2815/01/2011Saturday10:4016/01/2011Sunday
16:3028/12/2010Tuesday13:0220/01/2011Thursday13:0624/01/2011Monday
15:0827/01/2011Thursday17:4029/01/2011Saturday
The out of hours column is where I figure I need an IF statement but not sure which way to approach it best.
View 2 Replies
View Related
Aug 1, 2014
I am looking for a formula that counts from each particular month
The main List has the following
Column C has all dates
Column D has hours worked
Column E has engineers name
The Second List is broke down into months and hours
Column I Has month ie I4=Jan 14, I5 = Feb 14 etc
Column J I need to have a total taken from column D from the month in column I
View 8 Replies
View Related
Jul 25, 2002
How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.
View 9 Replies
View Related
Jan 15, 2009
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
View 9 Replies
View Related