# Combining Formulas - Production hours

#### robrobby

##### New Member
I've been modifying a sheet which works out the remaining business hours from a production run and gives me the end date and time, so that I can easily see when the current order is going to be completed.

At the moment the formulas to do this is spread across many cells, the formulas works great but it's very untidy as I have to hide the calculation columns.

What I would like is for all the formulas in each cell H5:N5 to be combined into one cell G5, But I do not have a clue where to begin.

https://www.dropbox.com/s/p04vdwlmn7jfxhy/ProductionHours.xlsx

Would anybody be able to help me out on this?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Welcome to the board.

First of all, there's nothing wrong with splitting calculations across multiple cells.
Sometimes it's actually preferred, if some of the intermediate calculations are repeated in other formulas.
If it's just an annoyance, you can move the formulas over to say AA5:AG5.
Then have G5 refer to the final calculation (presumeably in AG5 - originally N5)
Perhaps even put the formulas on another Sheet to get them completely out of the way.

That said, many people here can't (or don't want to) access filesharing sites, so we can't see your document.
It may help to post each of the formulas in H5 through N5.

Last edited:

I did consider moving the formulas to another sheet, or hiding the columns, but I know the users who will be using this will either delete/insert rows, or try copying the formulas down without copying the hidden cells.

I've created a cut down version below. I would like the combined formula in G5 if possible

 A B C D E F G H I J K L M N 1 Morning start Time Evening End Time Total Work Hours Per Day 2 7:00 22:00 =B2-A2 3 4 Date of Check Time of Check Part Number Qty PPH Remaining hours End Date/Time Combined Start Date Time length full workdays part workdays start+length Calc1 Calc2 5 27/3/13 10:00 MyPart001 7680 140 =ROUNDUP(D5/E5,0) =WORKDAY(H5,J5+N5,\$S\$5:\$S\$31)+M5 =A5+TIME(B5,0,0) =F5/24 =INT(I5/\$C\$2) =I5-(J5*\$C\$2) =B5+K5 =IF(L5>\$B\$2,L5-\$B\$2+\$A\$2,L5) =IF(L5>\$B\$2,1,0)

<tbody>
</tbody>

Last edited:
In this case, I'm gonna have to strongly urge you to leave it as is.
There are so many repetitions of the same calculation, that if it's all combined into one formula the same calculation ends up being executed many times.
This is very detrimental to performance of your sheet.

I will however tell you how you can do it yourself if you must...

You have the representation of all the formulas there.
Begin with G5

=WORKDAY(H6,J6+N6,\$S\$6:\$S\$31)+M6

Take the reference to H6, just copy the formula in H6 (minus the = sign) and paste it into the G5 formula in place of the G5 reference.
so it becomes
=WORKDAY(A6+TIME(B6,0,0),J6+N6,\$S\$6:\$S\$31)+M6

You may need to add () around the part you paste in to ensure the order of math operations happen in the correct order.
So you might make that like this instead

=WORKDAY((A6+TIME(B6,0,0)),J6+N6,\$S\$6:\$S\$31)+M6

Then do J6

=WORKDAY((A6+TIME(B6,0,0)),J6+N6,\$S\$6:\$S\$31)+M6
becomes
=WORKDAY(A6+(TIME(B6,0,0)),(INT(I6/\$C\$3))+N6,\$S\$6:\$S\$31)+M6

Just continue repeating that until all references to H6:N6 are gone from G6

Last edited:
Not only because of formula performance, but ease of reading.

Your resulting formula after combining it all into one, will be so long and convoluted it will be near impossible to read and understand what it's doing.
Keeping calculations seperated the way they currently are allows a representational display of what each part of the calculation is doing.
Much easier for reading/understanding and more importantly for troubleshooting.

Many thanks, I can now see how to do it

I'll take your advice though, as you say, it can become very detrimental and hard to maintain.

Thanks for spending the time in helping me do this. Very apprectiated

Rob

Glad to help, thanks for the feedback.

As far as users messing things up, I would say go ahead and put the formulas on another sheet and hide that sheet.
If that doesn't help, then "Educate" your users...lol.

Replies
3
Views
178
Replies
4
Views
184
Replies
3
Views
175
Replies
3
Views
334
Replies
10
Views
618

1,203,263
Messages
6,054,434
Members
444,725
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back