Help! Excel sume of time values question

[RobertN]

I need to add up a bunch of time values. It is somewhat simple, because they are in one column, in Excel. Many other data points and descriptors are in other columns/rows, but they have no net affect on the value I need.

For part of this, I need to add the overall time in hours and minutes within this column. All values are entered in hours and minutes, "1:00" or "2:35" for example. The data points are all within "Column-E", from "row-6 to row-35".

How can I make Excel add all those times, and give me a final sum? I have no clue about this type of function /forums/images/graemlins/blush.gif

Thanks!

 

[Gatorboy]

=SUM(E6:E35) should do the trick. Just make sure your cells are formatted for TIME.

 

[RobertN]

You're awesome! I kinda got the sum, but a sum of just 1:00, 2:35 was gibberish. I did not know about setting the columns and or rows in a format for time. That was the trick /forums/images/graemlins/laugh.gif

 

[RobertN]

One more question /forums/images/graemlins/shocked.gif

Now I have an accumulated time. I want to add it to a start time, like 8:00am. Now I want to figure a stop time. So, I have this start time but hacan't figure how to add the cumulative duration to it, and have the final figure come out in standard clock time, ie 12-hour or 24-hour format.

For example, I have a start time of 8:00am. If I add the cumulative time of 7:43 to it, it should show an end time of 3:43pm.

After you got me on the right path with the first thing, I found the formatting stuff. But, I have not been able to figure out how to format this /forums/images/graemlins/shocked.gif

 

[Gatorboy]

Just add the cumulative time to your start time using the plus '+' sign.

Example: Your cummulative time (7:43) is in cell E30 and your start time (8:00a) is in cell F10, the formula would be =E30+F10

Under the Format Cells..., you can change how it is displayed, so format the cell start time to Time and pick the display as 1:30PM.

 

[MadReferee]

1. Format the cell that will contain the final time as TIME with the TYPE being the desired output format (ie. 6:00 AM).

2. set the value of a cell to your start time

3. set the formula in the final time cell (#1 above) to add the start time cell (#2 above) and the cumulative time cell (ie =A2+B2)

You should now have the correct time.

 

[RobertN]

Thanks Guys

It works like a charm! /forums/images/graemlins/laugh.gif /forums/images/graemlins/cool.gif /forums/images/graemlins/smile.gif /forums/images/graemlins/grin.gif

Oh, what it is for... We have four controllers with a total of 36 irrigation stations. Most is for drip setups. We have two houses(house and Granny flat). The Granny flat(complete with Granny /forums/images/graemlins/wink.gif) is about 70' higher than the well site(one well on property). In addition to timing things so various programs across the three controllers do not overlap, we also have to be careful about when things run, as water pressure drops at the granny flat when we run yard water. At the well, best case is 70psi. With elevation/head pressure, best Granny sees is about 30psi.

She is going to put in a storage tank/jet pump at her flat, but it is some months out. So, we are trying to make good water scheduling, and keep some pressure at her place during peak hours.

Thanks again for the help! /forums/images/graemlins/laugh.gif