Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

How To Calculate Manhour Impact

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi Excel

I need to calculate the manhour impact on the production day (excluding Sat and Sun) based on the below ticketing reporting. The manhour per day is 8 hours. Sample data as attached.

Andy Wong

View Answers     

Similar Excel Tutorials

Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...

Helpful Excel Macros

Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
- This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t
Email Current Workbook & or Other Attachments
- This macro will send the current workbook in an email through Microsoft Outlook. The Macro allows you to send the most
Remove Comments from a Workbook in Excel - All Comments Deleted - Macro VBA
- Excel macro that will remove/delete all of the comments from an entire Excel workbook. This means that this macro will

Similar Topics


I have created a pivot table that displays man-hour bookings from each department in a company every month. I have transfered this data onto a pivot chart so that i can see a line graph that shows the increase of manhour bookings throughout the year from each department.

I have the department number as a field on the left and the months are displayed on the x axis. the manhour booking numbers are what is being plotted in my chart.

What i need to do now sounds very simple but i cant get it to work.

I need to add in a trendline with an average number that has a constant increase throught the year e.g 250 per month = 3000 by the end of the year. All i can seem to do is add a trendline that matches the current data. I want a seperate trendline with my own numbers so i can compare the booked manhours to my own budget manhour trendline. Can this be done?? When ive tried adding in the data into the pivot table to pull accross to the chart it adds everything together!

please help.

I have tried to find this query on the forums but haven't had much success, so apologies if I've missed it...

I have a range of approx 100 cells in column Q that each contain one of the following text entries;

Implemented - Impact - Configuration
Backed Out - Impact - Configuration
Implemented - Impact - H/W Error
Backed Out - Impact - H/W Error
Implemented - Impact - S/W Error
Backed Out - Impact - S/W Error
Implemented - Impact - New Functionality
Backed Out - Impact - New Functionality
Implemented - Impact - xxx
Backed Out - Impact - xxx
Implemented - Impact - yyy
Backed Out - Impact - yyy
Implemented - Impact - zzz
Backed Out - Impact - zzz
Implemented - No Impact
Backed Out - No Impact

What I want to do is count how many cells contain "Backed Out - Impact" and also how many contain "Implemented - Impact", and am trying to use the following formula that Excel does not like...

=COUNTIF((LEFT($Q$4:$Q$94, 19)),"Backed Out - Impact")

Any ideas what I am doing wrong here...?


I'm working to calculate the Probability & impact of the following or what's called a PID Score

Basically, I have a table with 5 options ranging from Very Low to Very High

Then, I have a Probability & Impact column

In a 3rd column, I'm seeking to get the maximum PID or Probability & Impact statement, that will result with one of the range call outs.

I attached an example file.


hello everybody,

I'm not an expert in excel,

Attached here is the sample of my man-hour sheet, I already have my formula but i think there is a problem on it.
the scenario goe like this,

I want to compute the manhour consume of my personnel in a day but I need to deduct the 1 hour breaktime which is usual and another breaktime at around 5:30 pm up to 6:00pm.
Kindly help me guys...

Thank you

Hi All,

Good day to everybody. I am a new member of MREXCEL.COM I found this Forum through the help of SEARCH option from the internet and I became interested due to some reason. Since Excel is part of my daily jobs I decided to become a member. I know that the GUYS out there can help me out of my problems. Thank you for having this sort of Forum.

Guys, my question is, how can I use "Vlookup" if the reference value is the same as shown below. What I know only if the reference value is not the same. Since my reference value under the Activity ID column is the same, what is formula to be used when I need each value under the Manhour Column. Please HELP. Thanks in advance.

Activity ID
Activity Name
Resource ID Name
Budgeted Units

Grid Y11-Y33 to X18 & X24 RSMCAFO060 SM/CA - Blinding & Waterproofing Blinding Concrete 29.16 m3 262.44 RSMCAFO070 SM/CA - Steel Fixing & Shuttering Reinforcement 142.27 Tons 3556.75 RSMCAFO080 SM/CA - Concreting (X18=19 Nos. & X24=21 Nos.) Concrete 946.60 m3 946.6 RSMCAFO080 SM/CA - Concreting (X18=19 Nos. & X24=21 Nos.) Number of Pile Caps 40.00 Nos. RSMCAFO080 SM/CA - Concreting (X18=19 Nos. & X24=21 Nos.) Formworks 1,063.49 m2 1595.235 RSMCAFO060 SM/CA - Blinding & Waterproofing Water Proofing Membrane 1,797.74 m2 1797.74 RSMCAFO060 SM/CA - Blinding & Waterproofing Protection Board 1,231.87 m2 1231.87 RSMCAFO060 SM/CA - Blinding & Waterproofing Screed 496.02 m2 223.209



******** ******************** ************************************************************************> Microsoft Excel - AV-RequirementsOverhaul.xls ___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout F8 =
A B C D E F 3 Task Manhours Sun Mon Tue Wed 4 Clean 1 X * X X 5 Training 2 * X X X 6 Lubricate 0.5 X X X * 7 Repair 0.75 X * X * 8 Fabricate 3 * X X X Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box


I have a scheduling tool I made to help my team of planners distribute work evenly over the course of a year. Right now the spreadsheet that I have developed is WAY too big (30mb). This is due mainly to the way that I am calculating daily manhour totals, which I'm sure is highly innefficient.

As a rough example, I made the table above. What I need to figure out is a dynamic way of summing manhours for any given day. Where an "X" is placed, the corresponding task in that row is scheduled for that day.

So Sunday = 2.5
Monday = 5.5
Tuesday = 7.25
Wednesday = 6

Is there a way to sum based on those criterias without writing a macro that I need to run every time data is changed? The user needs to be able to see changes on the fly. Right now all of my calculations occur without user intervention, but it takes about 5 seconds to recalculate my cumbersome, inneficient excel formulas! That adds up to a user headache sometimes! (Currently I have different sheets set up that indirectly link to the sheets with data and convert the "X"s to manhour totals)

So is there a way to do this in one cell for each column that I just can't figure out?

Thanks in advance for any help I recieve!

1305.50 is the number.
13 meanin 2013
05 meanin 1st week of 2013.
.50 meanin 50th model.(irrelevant to problem).
1305.50 this means 50th model was dispatched in 5th week of 2013.

drill(hours) roll(hours) paint(hours) dispatch
123 100 50 1305.50
123,100,50 are hours required to do a specific job.which were done and hence job was dispatched on 5th week of 2013.

but while calculating i forgot to consider weekend holidays.(saturday & sunday & holidays)
shift time for 1 day is 15 hours.
so i want to go backwards and calculate the actual dispatch date minus the weekends and holidays.
(say 22nd jan2013 tuesday was a holiday )

Hi all,

I am trying to create a pivot table in VBA from a table with the following data:

TID RID Consequence Weighting Impact 5 10 2 1 2 10 118 2 1 2 10 119 2 1 2 10 120 2 1 2 2 133 2 1 2 10 155 2 1 2 3 181 2 1 2 98 278 5 1.5 7.5 100 278 3 1.5 4.5 102 278 1 1.5 1.5 3 278 2 1.5 3 98 285 5 1 5 100 285 3 1 3 10 304 2 1 2 3 310 2 1 2 5 310 2 1 2

The pivot needs to display the following:
Data =
- MIN of Impact
- Max of Impact
- Mean of Impact
- STDEV of Impact

I can't seem to make this work, I can get all the other values for Impact by RID but not STDEV

Pls Help

Currently I have 2 DB linked together with a query.

THe first DB is a quality review, the second is the impact review. multiple people will be logging data in both (quality team/Impact Team)

In the quality DB there is a subform that queries the impact DB based on a ticket number, the ticket number field is embeded into the quality form.

This will allow the quality team to enter a ticket number for the impact and essentially tie the quality review with the impact for that day. All this works great.

I then use Excel and a basic pivot table to view the data in both DB to understand the scope of impact, the issue that I have is since the Subform is a query from the impact DB I cannot view this data that it pulls in through Excel. I think I need to create a composite Key?? ( You can only see the data in the access Form)

For each Impact review; the data will be logged for each day, so there could be several impacts for one day but logged with several ticket numbers defining each impact, BUT in the quality DB there is always multiple quality reviews for 1 day, so I need the functionality to create a composite key from the Impact DB based on the Ticket number and Date field combination, then with that I could build another query to pull the data into the quality reivew DB so I can see it in Excel??

I guess it would be a dynamic table that links the 2 DB together?? I did try the composite Key selecting the 2 fields but I got an Error that the fields cannot be NULL, there is already a primary key as AutoNumber. Do I need to delete this?

Any help is appreciated.


How to set the formula if less than 29 minutes return to 0.5 hour and more than 30 minutes return 1 hour. It show in column D.

Per below, the four criteria add up to a miximum score of 10.

After someone enters in the raw scores for each of these four criteria, I need the raw score sum (Business Impact Score) to designate an appropriate Business Impact Rating based on the following:

Significant = 8,9 or 10
Moderate = 4,5,6 or 7
Minimal = 0,1,2,3

Criteria Wt. Raw
Influence 3.0
Immediacy 3.0
Importance 2.5
Intent 1.5

Business Impact Score
Business Impact Rating

Hello all,
I need some help with parsing a string based on several delimiters, and place them in to arrays.

Here is an example string that needs to be parsed
"A:Test,Test2; B:Sample, Sample2;C:Toparse1, Toparse2"
This string must be parsed via ( : , ; )and put into two Arrays labeled Impact, System
The resulting set should look like

Impact System
A Test
A Test2
B Sample
B Sample2
C Toparse1
C Toparse2

Clarification: each Impact is separated by ; and within each impact system is separated by ,
The System name has a : as you can see from the string.

Any help would be greatly appreciated... Thanks!!

Michael Chang

Hello All,

Could anyone please help me with this one? The first table shows the data from January to currrent month. I want to sum the number of error items based on day 1 or day 2 error for each error party. Secondly, I want to sum the $ impact based on the same two conditons (error party and day 1 or day 2).

Any help is vey much appreciated. I have been manually calculated this for over a year.

Thanks a lot!!!!

Date Bank VGI # Bank # Fund Name $ Impact Error Party Error Day (1st or 2nd) 01/02/08 Citi $ 1.00 CM 1 01/03/08 USB $ 2.00 Bank 1 10/01/07 USB $ 3.00 TA 1 04/08/08 USB $ 4.00 SPPP 1 04/08/08 JPM $ 5.00 LTTT 2 06/08/08 JPM $ 6.00 MMU 2 06/07/08 JPM $ 7.00 LTU 2 07/08/08 JPM $ 8.00 CM 2 07/10/08 JPM $ 9.00 Bank 1 08/01/08 JPM $ 10.00 Bank 1
Group Day 1 Errors $ Impact Day 2 Errors $ Impact CM Bank TA SPPP LTTT MMU LTU

Hi I am having a bit of a trouble calculating standard deviation of a set of values for my work.

I have been given a set of data. The contains two value gas production over time. Like the following graph > > rate11.gif

[Hours] [Gas Production/Lit]
0.00 0
0.25 10.3
0.50 20.5
0.75 30.8
240 1903.4

I have been asked to calculate standard deviation. I have not been told anything else. Just the exact phrase - "calculate standard deviation to understand the error in our results".

I know how to calculate SD in excel, but I am simply lost as to what to calculate in this data set. If I calculate usual SD I get a value that is close to 500 or 600.

Can someone please help me by pointing me to the right direction please?

Many thanks.


I have this spreadsheet with two variables: PROBABILITY and IMPACT in two different columns & each falling in the scale of 1to 5. Now each record (data in a row) may have a value between 1 to 5 for PROBABILITY as well as IMPACT.

First I need to sort the data in the worksheet in ascending order (keeping the reference number of tow item in the sort scope) with records starting with PROBABILITY of 1 and IMPACT ascending from 1 to 5 and so on until records with PROBABILITY of 5 and IMPACT ascending from 1 to 5.

The table will look like the tables in image attached: table left (Before sorting) and right (after sorting)

Now I need to copy the reference numbers in the matrix presented at the bottom of the image in such a way that all the reference numbers with Probability 1 and varying Impact score will be sequnetially pasted in the cells of the squares with Probability 1 and Impact 1 to 5 (bottom most square blocks in the graph.

I know how to do the sorting through Data --> Sort option. Can some one help me with a macro (VBA code) to copy the reference numbers in the cells in the graph according to the values of Probability and Impact matrix.

your help is highly appreciated.


Hi everyone,

There may be a thread regarding this already and I have tried searching the web for about 2 hours looking for an answer with no joy.

I'm trying to make a base calculator based on binary, decimal and hex so that what ever number (or letter) I enter gets automatically calculated to the other the other 2 bases.
I'm doing an access course and I've been told to create a calculator to help me calculate from every possible way. I have found a some web based ones and even found an excel based one, but I want to create one myself so that I can understand excel a bit better.

Any help would save my laptop and would be greatly appreciated too.


P.S I'm looking to add a button to calculate but have no idea how to write a macro yet, but would this have any impact on the formulas in the cell?

I am trying to write a nested IF statement to return the Priority for a combination of Impact and Urgency (as per ITIL table) it was going great until I hit the maximum of 7 nested if statements when there are 9 possible combinations.

I then had an idea using OR as well as several combinations of Impact and urgency give the same priority but I am not sure how to reflect this.

for the purposes of this exercise Impact is in G2 and Urgency in H2. The ITIL compinations are shown below

Impact Urgency
Low Medium High
Low 5. Planning 4. Low 3. Medium
Medium 4. Low 3. Medium 2. High
High 3. Medium 2. High 1. Critical

so I can say if impact is Medium and Urgency is Low OR if Impact is Low and Urgency is Medium then Priority is 4. Low but I do not know how to contruct that as an if statement.

Titles not very descriptive but i dont know how to describe this.
I want to reduce a number by the weighted amount of another figure.
A1 = 95 and B1 = 1
A2= 95 and B2 = 10
A3 = 94 and B3 = 1
A4 = 94 and B4 = 10

so the best one is obviously the first A1, then A2 then A3 then A4
If you add in this one
A5 = 85 and B4 = 1 and change A4 = 94 and B4 = 100 then
In fact anything in B higher than 20 is quite significant. Thats not to say that a B cell = 19 and an A cell of say 96 is going to be first, there might be some minor adjustment that puts it second but the impact is fairly minimal until The B cell gets to 20.
A=High B=Low little impact on position
A=High B=Med small impact
A=High B=High large impact

Any ideas if you can understand this would be much appreciated.

Can anyone help with this issue?

I'm trying to calculate the # of hours between the two dates, excluding holidays and weekends, with the standard work day equal to 8am to 5pm.

See the attached file for example. The highlighted cells should logically show 1.5 hours, but I always show 9 hours using network days.

Any help is appreciated.

I need to calculate a complicated weighted average... it is more tricky than most and I can't seem to logic my way through it w/o massive nested if then statements.


Assume I start at x=85% and then move to y=95%. The impact of the move is bracketed by two ranges (i.e. 85% to 91% and 91% to 95%). Thus, the overall impact is (91%-85%)*2 + (95%-91%)*4.

There are multiple moving parts... the variables x and y can both change. the ranges are fixed values, but there could be 2 or 3 (i.e. x=55%, then ranges defined at 0-75, 75-91, 91-100)--- so solution would be (75-55)*1+(91-75)*2+(95-91)*4...

Any ideas of how to simplify this one?

I need to calculate the profit impact of system downtime. The impact is dependent on a number of things, including the length of time, the purchasing channels affected and the products affected.

I want to produce a little spreadsheet that anyone can use that will generate the cost for any set of circumstances. So they need to be able to choose the channel - 1 or more of about 10, and the product - 1 or more of 5. There will be another couple of categories too, with maybe 5 options in each.

Each category option will have have a multiplication factor associated with it - if more than one option is selected, the factors will be summed.

I think I probably need checkboxes, but they seem quite fiddly to set up. Is there a better way?

Hi all,

Please can you help me with this problem I'm facing. I am creating a risk register and to assess a risk I am using a matrix (see file attached). I am not skilled enough with excel and arrays or whatever I need to do this so i am asking here. I basically need the value in the 'assessment' column to be populated in my register depedning on which values are entered in the 'probability' 'cost impact' and 'time impact' columns on the register. e.g. if the prob is 95, cost is 900,000 and time impact is 50 - VHI will be entered in the the assessment column.

Please shout if this doesnt make sense. i really hope you can help me with this.

many thanks.


"I am having problems with writing a formula in excel that will satisfy 2 constraints and select the most optimal answer within in a column.

The 2 constraints that it must meet a
1. MOH (materials on hand) must be between the range of 0 and 12.

2. Excel must select the "impact" with the greatest negative number.

If these 2 constraints are satisfied, then it should return its corresponding MOQ.

If the MOQ (minimum order qty) provides the greatest negative number for total impact while still being within the range of 0 and 12 for the MOH, excel shall display this MOQ.

I am trying to write a formula for the information below:

1 MOH Limits
2 0
3 12
4 MOQ 200
5 MOH 2.662229617
6 Total Impact -41.14089368
7 MOQ 300
8 MOH 3.993344426
9 Total Impact -44.88003562
10 Equation 200

The formula I have is:

I keep track of risks. Risks have two parmameters; probability of occurrence and business impact if it occurs. Both of these factors are usually rated in a L-M-H manner. I often sort by these factors so I've used numbers to facilitate sorting, e.g., 1=High, 2=Med, and 3=Low and is the same for both parameters.

What I'm trying to do is create a composite measure of each risk so that all 9 permutations can result in a unique number. This way I can prioritize the risks based on their overall "risk rating." My intent is to show the coun't of risks in a 3x3 grid. The problem is my math ability. How can I come up with a calculation that results in 9 unique values based on this? For example, since 1*2 is the same as 2*1, I cannot easily distinquish a low probability risk with medium impact from a medium probability risk with a low impact, which would result in a different placement on the grid.

Since nested IF statements can only handle 7 situations, and I have 9, I can't take that approach. I thought about a sumproduct approach from within each square on the grid, but that gets messy. I figure a single number will make it quite easy to count.


Thanks in advance. Wayne

What form. Can I use to separate the 6 digit from the below:

Deep Impact 746893

To get just the name: Deep Impact