Email:      Pass:    Pass?

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

Similar Excel Video Tutorials

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...?

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!

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.

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

I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1. If this doesnt make sense I appologize, I'll try to answer any questions.


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.


Hello experts,

Im trying to pull certain data from this "data table" (example below) and have certain parts import into a report (separate excel sheet). I tried several times with different combinations of formulas and I dont think its possible unless it uses complex combinations of formulas which I dont want to use. Also the data table may update from time to time but the overall layout should stay the same. I think the best way is VBA.

-For example if given "ZIM0001" I will need to pull out almost all of the corresponding information (data next to the cells labeled "ZIM0001") into certain cells in the report. For example, when given "ZIM0002" in one cell I may need to pull in "Test Location 1" and "Mid Thickness" and "Elongation test 1" and "18.000" and "35.000" all into certain cells in the report.

With VBA this shouldnt be too hard but it would need to be easy enough for me to work with and change cells around in (which I could easily do).

(I can email this example data table if needed, I couldnt get the HTML maker)
HTML Code:

ZIM0001	Impact 100 Structural/Bar	IM100	CH1_SMP1	Charpy - test 1 sample 1	330		20.000000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_SMP2	Charpy - test 1 sample 2	340		20.000000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_SMP3	Charpy - test 1 sample 3	350		13.300000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_AVG	Average Charpy test 1	360		20.000000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_TEMP	Charpy Test Temp. test 1	370		-40.000000000	-40.000000000
ZIM0002	Impact 100 No CVN	IM100N	TENSLOC1	Test Location 1	210	Mid Thickness	0.000000000	0.000000000
ZIM0002	Impact 100 No CVN	IM100N	TENORIN1	Test Orientation	220	Longitudinal	0.000000000	0.000000000
ZIM0002	Impact 100 No CVN	IM100N	YIELD_1	Yield Strength test 1	230		100.000000000	999.000000000
ZIM0002	Impact 100 No CVN	IM100N	TENSIL_1	Tensile Strength test 1	240		110.000000000	135.000000000
ZIM0002	Impact 100 No CVN	IM100N	ELONG_1	Elongation test 1	250		18.000000000	35.000000000
ZIM0002	Impact 100 No CVN	IM100N	ELGAGE_1	Elongation Gage Lgth test 1	260		2.000000000	8.000000000
ZIM0002	Impact 100 No CVN	IM100N	REDAREA1	Reduction of Area test 1	270		40.000000000	999.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	TENSLOC1	Test Location 1	210	Mid Thickness	0.000000000	0.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	TENORIN1	Test Orientation	220	Longitudinal	0.000000000	0.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	YIELD_1	Yield Strength test 1	230		100.000000000	999.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	TENSIL_1	Tensile Strength test 1	240		110.000000000	135.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	ELONG_1	Elongation test 1	250		18.000000000	35.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	ELGAGE_1	Elongation Gage Lgth test 1	260		2.000000000	8.000000000

Any help greatly appreciated,

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.


Hi All,

I have an Excel (one of my team member entering the data in to Excel from application, which contains date, service, problem, impact, rootcause. The excel named as majorincidentkeyrisks.

I am using SQL Server 2008 reporting services to publish the service desk reports, which contains above Excel as datasource.

I have query :

select date, service, problem, impact, [root cause]
from [majorincidentskeyrisks$]
where date>=@startdate and date<=@enddate

But date is coming in dd/mm/yy hh:mm:ss format, I need only date,month,year i.e dd/mm/yyyy format only.

how can I modify the above query to get results only in dateformat.

please help me.

Thanks in advance

venkata peri

"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

In XL2007 I am importing several lists from SharePoint2007. The import goes fine, however, I cannot get rid of (or clear) the formatting that gets applied to the imported cell range.

Clear all has no impact.
Select style "none" has no impact.
Set background color to none has no impact.
Set list theme (maybe wrong words) to pink does change the area.

What to do to get rid of all formating and KEEP it that way?

**** Penny


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.


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,