Combining Formulas - Production hours


New Member
Sep 14, 2012
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.

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:
Upvote 0
Thanks for the quick reply and advice,

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

1Morning start Time
Evening End TimeTotal Work Hours Per Day
4Date of Check
Time of CheckPart NumberQtyPPHRemaining hoursEnd Date/TimeCombined Start Date Timelengthfull workdayspart workdaysstart+lengthCalc1Calc2

Last edited:
Upvote 0
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


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

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


Then do J6


Just continue repeating that until all references to H6:N6 are gone from G6
Last edited:
Upvote 0
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.
Upvote 0
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

Upvote 0
Glad to help, thanks for the feedback.

You've made a wise choice.:)

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
Upvote 0

Forum statistics

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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