Subscribe for Free Excel tips & more!
E-mail:

# How To Calculate Manhour Impact

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.

Regards
Andy Wong

## Similar Excel Tutorials

Calculate Someone's Age in Excel
How to automatically calculate someone's age using Excel.  This method is simple and will update every year so tha ...
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 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 formu ...
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 ...

## 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

Hi,

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!

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

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 )

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.

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

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.

Many thanks.

Hi

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.

Thanks
Venkat

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.

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.

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.

Anthony.

"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:

A B C
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:
"=IF((AND(C5>\$B\$2,C5\$B\$2,C8

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.

Ideas?

Thanks in advance. Wayne

Hello. I am multiplying two rates to get to a TOTAL. In the next cells, those rates changes and the TOTAL changes. Is there a formula in excel that I can create that will show the impact that each movement had on the TOTAL? Spreadsheet attached. Thanks.

Hi,

I need to calculate end dates based on project/task start date and the number of hours it takes to complete that particular project/task. Now if its a 24hr workday i can simply use the workday function, but i need a formula to calculate end date based on an 8-hour workday (working 8 hours every weekday, shift timings does not matter or you can assume 9:00AM-5PM) and also carry over the remaining hours to the next day.

As in, if a task takes 14 hours to complete, then 8 hours go to the first day and the remaining 6 hours adds up with the next task's hours and if that sum exceeds 8 hours, it goes to the next and so on.

Please take a look at the sample data, where I have manually calculated the end dates and see if they make any sense. I have also attached a worksheet with the same data.

Task Estimated hours Estimate in Days Start Date End Date
Task 1 4 0.50 12-Jul-10 12-Jul-10
Task 2 3 0.38 12-Jul-10 12-Jul-10
Task 3 12 1.50 12-Jul-10 14-Jul-10
Task 4 5 0.63 14-Jul-10 15-Jul-10
Task 5 6 0.75 15-Jul-10 15-Jul-10
Task 6 10 1.25 15-Jul-10 16-Jul-10
Task 7 8 1.00 16-Jul-10 19-Jul-10
Task 8 3 0.38 19-Jul-10 19-Jul-10
Task 9 5 0.63 19-Jul-10 20-Jul-10
Task 10 5 0.63 20-Jul-10 20-Jul-10

Is it possible to calculate something like this without VBA?

Thanks for the help in advance.

Hi,

I have been asked to put together a quick production schedule in Excel 2003 to give us an overview of our workload with remaining hours spread out between the Start Date and the End Date based on the # of days per Week. I am struggling with how to use a formula to spread the time throughout the weeks.

I have attached a sample spreadsheet, any help would be greatly appreciated.

Thank you,
Sandi

Hey,

I have searched the forum, but did not find the exact solution to my problem.

I am currently in a case competition where my group manage a virtual company.
We have workers employed, and i'm trying to calculate the how much we spend on overtime.

We calculate overtime quarterly, where each worker maximum can work 420 hours on his basic salary.

Then we have 2 overtime rates for further production:

Rate 1 = +50% of basic wage - the worker can max. work 84 hours on this rate, before we have to pay him rate 2.

Rate 2 = +100% of basic wage - again the worker can max. work 84 hours on this rate.

When I paste a number into cell D5 (the attached excel worksheet), I would like 'excel to calculate' my total wage spendings i cell D26, based on the different rates.

I hope my question makes .

Regards,
Unigeek

Hello Everyone,

I have really been struggling with this impact analysis...I think it is really a sensitivity analysis, but I just can not get it to work right....I am missing something very simple I think.

Here is the situation. I have many customers (more than 100) that all contributed to a total change in ASP (average sales price) through their price and volume from month to month. I need to understand how much each customer's impact was on the total ASP change and was it mostly price or volume.

Attached is a model I thought would work (using a 3 customer approach)....but it doesn't seem too. And then, if it did work, how can I do it on a large scale, looking at more than 100 customers in this approach will take a very long time.

Many thanks,
Matt

I need to calculate the impact of allowing our customers to buy a product at full price today or to pay some % up front and then a monthly payment.

For example, they would be able to choose to pay \$250 one time or \$50 up front and then \$200 over the next 11 months (split evenly) with a cost of capital of 1%/month.

What's the best way to set this up? Any help would be appreciated.

I have four employees producing widgets, and each has been assigned a target that is out of proportion with their throughput. This has an impact on the overall time taken to complete the production order (see attached Excel snapshot image).

Is there a formula/function I can use to distribute the load to match each employee's throughput, and level out production to complete the order in the shortest possible time?

FYI,

The month of January shows each employee's target, which decreases each subsequent month based on the number of widgets expected to be produced based on their maximum output.

Many thanks.

Hi there!

Here's my problem.. I have a spreadsheet that I am using to try to track Project Man Hours.

What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.

i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.

What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.

i.e.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.

Does any of this make sense?

I'm terribly sorry but I'm having a hard time explaining this.. I'm going to attach my spreadsheet so you can understand what I mean..

Eventually I want to have a data input worksheet and a manhour chart report for each Project and then all the projects flood into one master chart for all Projects.. And then I want to be able to email that and the individual reports via the worksheet with some sort of macro button or something that opens up a Lotus note new email automatically with the worksheet to send off to who my boss chooses...

But I'm going to take it one step at a time so I dont overstay my welcome.. haha..

Thanks in advance for anyone's help!

*Crossposted here http://www.mrexcel.com/forum/showthread.php?t=374394 because I, like everyone else of course, is on a deadline.. haha. Thanks again!

How to calculate the difference in hours excluding saturday and sunday, inclusive of the business hours

for example: if start date is 06-09-2011 15:24 and end date is 12-09-2011 17:20, business hours are from 12:00 to 20:00

When I calculate it should give me the result in hh:mm format by excluding saturday and sunday, inclusive of the business hours

Hi I'm working on a bi-weekly time sheet that goes as follows:

Each work week is 35 hours (Mon-Fri 7 hours a day)
After 35 hours of work the first 5 overtime hours go into comp time each week.
After those 5 hours of comp time the rest of the hours worked will be in time and a half.

So for example I work Mon & Tues 9am-11pm (excluding one hour for lunch each day) that's 26 hours.
14 hours regular, 5 hours comp, 7 hours time and a half

I need a column which will calculate my 5 hours comp time and then once the five hours are summed up it then turns over into the time and a half column. Is that possible? I am very new with Excel. Any help would be appreciated!

Here is an attachment of what I've got so far:Jessica Time Sheet.xlsx

Hi,

I am developing an Impact Matrix sheet utilising the scatterplot chart function. I have it working nicely except I cannot label the points to identify which point is which opportunity....

To the uninitiated, an impact matrix plots 2 variable against a series of ideas, projects etc and allows easy visual identification of which opportunity will have the biggest impact, e.g out of our 20 projects, which will be the quickest to implement at the least cost, you then rate each individual project in terms of how much and how long, run the graph and which ever is closest to the top left is the one you do first....

Enough Black Belt malarky! Anyone got any ideas how to label the dots on teh scatterplot? Even if they were just numbered as at present, it is impossible to see which is which!

Thanks in anticipation,

Stu

Hi,
I think this is a fairly straight-forward problem to most advanced Excel users. I'm quite new to the whole thing and need to quickly produce these results, so any help is appreciated. I searched on the internet and couldn't find an adequate solution

Here is some sample data:

URL | PageViews High Impact
www.mysite.com/url_1 3000 Y or (1)
www.mysite.com/url_2 236 N or (-1)
www.mysite.com/url_3 218 Y or (1)
www.mysite.com/url_4 4 N or (-1)

I'd like to create a four quadrant graph with page views as y-axis and high Impact as X-axis and the URL column as the label. The tricky part for me is that I'm not really interested in graphing the exact number of page views. I'm simply interested to know that if the page views is greater than some number (200 for e.g) and if it is it belongs to Quadrant I or Quadrant III depending on the value of High Impact.

Any VBA code or explanation on how to generate this graph in Excel would be greatly appreciated. If there are links that will help me go in the right direction that would be great too!!!