Adding Total of Hours and Minutes in Decimals

[jaclrsen]

Hi, I've got a worksheet attached where I'm trying to sum decimal numbers but have the end result appear in hours and minutes. For example, 5 cells have 1.25 (1 hour and 25 Minutes) each and when added I'm trying to make them equal 7:05 to reflect the hours and minutes involved. Please note, there could be hours and minutes which are higher than 60 as well like 2:80 as a random example. So two cells with 2.80 would effectively equal 6.40 when added. Is there a formula which would help me achieve this? Thanks, JL.

[6StringJazzer]

Sorry to be blunt but using 1.25 to mean 1 hour and 25 minutes is a bad idea. It's non-standard and inevitable that it will lead to errors when attempting to do arithmetic on them. Where did that come from? Also, in your description you said you want a result of 7:05 but your spreadsheet shows a result of 7.05. What format do you want the result in? Here is a file with two possible solutions, each with results in both formats.

[jaclrsen]

Hi 6StringJazzer, Thank you for your reply and for your solutions. Yes, I see what you mean and I appreciate you being honest about the bad idea which I acknowledge. I'm having trouble already when I'm getting into larger numbers. Yes, it should be '7.5' so I'm sorry about that. May I get some advice on how to best record 1 hour and 25 minutes and 2 Hours and 40 minutes as examples?

I've got a sheet where I've got running times and I have to enter an array of times and then total them. Just wondering on the best to enter them as hour and minutes and then sum them so they still come out as hours and minutes at the end. Thanks, JL

[GaryBarlow]

Not sure I understand completely., but if you format the cells as time (hh:mm) that may help. If you need to show hours over 24, then format as [hh]:mm

HTH

[jaclrsen]

Hi Gary, Thank you for the response. Yes, I tried that. However, it still didn't seem to produce the right figure when working with larger numbers. For instance, these are the exact numbers I'm adding in one of the scenarios: 2.15, 3.30, 3.45, 3.45, 3.45. Using the formulas and the format it comes out as 19:12 which I don't believe is correct. Thank you for the response though. Thanks, JL

[6StringJazzer]

Using your numbers in the file I uploaded, I get 17.0 (17:00). How do you get 19:12? From your post farther above, 1.25 and 2.40 also work correctly.

Maybe you have taken my example, and made an error in implementing it in another file.

[jaclrsen]

Hi 6StringJazzer, Yes, I got it to work. I didn't check the Format Cells properly but all good now. Thank you for your response. Just a couple of points on this.

1. If you make cell A6 > 111.25 instead of 1.25, it doesn't appear to add the numbers correctly. That's what I was getting at with the larger numbers originally as well. For example, adding 111.25 to A6 only makes the total 21.05 in I7 and F7.

2. When summing cells that are not in order, how is this formulated? For instance, this is the current formula:

=SUM(TIME(INT(A2:A6),100*(A2:A6-INT(A2:A6)),0))

Lets say the cells are in different cells like C7, C14, C21, C28 and C35 as an example. How is this done using the array? I tried this formula but to no avail:

=SUM(TIME(INT(C7+C14+C21+C28+C35),100*(C7+C14+C21+C28+C35-INT(C7+C14+C21+C28+C35)),0))

Thank you for your help. JL

[6StringJazzer]

My solution used the TIME function, which will not give correct results when the hours is greater than 24. So I had to rework the formulas and the formats. See attached. Note that the times are now using a custom format of [h]:mm to be able to show more than 24 hours.

Second, the formula that you are trying to change is an array formula. It does not use sums, it uses arrays. It's kind of a big topic to try to explain in one post but the idea is that the formula does the calculation separately for each cell in the range A2:A6. It is not adding them. So your formula should look like

=SUM(TIME(INT(C7,C14,C21,C28,C35),100*(C7,C14,C21,C28,C35)-INT(C7,C14,C21,C28,C35)),0))

and when you edit it, you must hit CTRL+SHIFT+ENTER, not just ENTER. You know you have done it correctly if you see the braces in the formula box after you're finished:

={SUM(TIME(INT(C7,C14,C21,C28,C35),100*(C7,C14,C21,C28,C35)-INT(C7,C14,C21,C28,C35)),0))}

But you can't just type in the braces, you have to use CTRL+SHIFT+ENTER.

[jaclrsen]

Hi 6StringJazzer, Thank you for your response. That solution worked very well and I appreciate all of your help with this. I'm still having trouble with using cells which are not continuous. I.E. I can sum a range like A2:A6 but when the cells are split like C7,C14,C21,C28 and C25, I kept getting an error. This is what I'm trying:

{=SUM((INT(C7,C14,C21,C28,C35)+100*(C7,C14,C21,C28,C35)-INT(C7,C14,C21,C28,C35))/60)/24)}

I've also tried a formula in Cell F15 of Sheet 2 where I've got an example of what I'm trying to do. Any further help would be terrific and thanks for all your help and patience with this. JL

[jaclrsen]

Hi 6StringJazzer, I was able to get the below formula to be accepted by Excel:

{=SUM((INT((C7,C14,C21,C28,C35)+100*((C7,C14,C21,C28,C35)-INT((C7,C14,C21,C28,C35))/60)/24)))}

However, I'm getting a '#VALUE!' error. I'm sure I've got a bracket or comma out of place but I'm not sure where. Any further help with this would be great. Thank you, JL.

[daddylonglegs]

As per other suggestions here, I think you are making this too hard for yourself. If you enter the times with a colon separator e.g. 23:45, 111:25 etc then excel will recognise the values as time values and sum them correctly, so you can sum them with a simple SUM function like this:

=SUM(C7,C14,C21,C28,C35)

Make sure you custom format the cell with that formula as [h]:mm - the square brackets signify "elapsed time" so will correctly show time values >= 24:00

In your example what time value does 67.7 represent - is that 67 hours 7 minutes or 67 hours 70 minutes (or something else)? If it's the former then to enter as a time value use 67:07 and if it's the latter why wouldn't you enter it as 68 hours and 10 minutes, i.e. 68:10

[jaclrsen]

Hi daddylonglegs, Thank you so much for your response. I've never really worked with time before so I was a little unsure of the best way to proceed with it. However, that's a very helpful solution and is just what I need. Yes, 67.7 would represent 67 Hours and 70 minutes and it's much easier to enter this time with a colon separator. I also wanted to acknowledge the tremendous help provided by 6StringJazzer. I really did learn a lot from his posts too. Thanks also to Gary for pointing me in the right direction.

Just for the record, what was wrong with this formula though:

{=SUM((INT((C7,C14,C21,C28,C35)+100*((C7,C14,C21,C28,C35)-INT((C7,C14,C21,C28,C35))/60)/24)))}

Thank you, JL.

[daddylonglegs]

You can't apply INT to a discontiguous range so INT((C7,C14,C21,C28,C35)) doesn't work - you can only apply INT to a single cell or a contiguous range like A1:A10.

If you have "time" values in those cells where the integer part represents the hours and the decimal part the minutes then you could use this formula to get the sum as a time value

=SUM(C7,C14,C21,C28,C35)/24+(MOD(C7,1)+MOD(C14,1)+MOD(C21,1)+MOD(C28,1)+MOD(C35,1))/36

For a contiguous range you could use this formula

=SUMPRODUCT(TEXT(C7:C35*100,"0\:00")+0)

....but that won't recognise values where the decimal part is >= 0.6 so you'd have to express 67.7 as 68.1 for that to work

[6StringJazzer]

Just to elongate daddylonglegs's answer, using a discontiguous range in an array formula will be accepted by Excel--it won't tell you it's an invalid formula. But it just won't work. I thought I had written a post on that, but I don't see it above so I must have forgotten to hit the Post button.

You need to either enter your times in the conventional way, or use my solution #1 in the first file I posted.

[jaclrsen]

Hi daddylonglegs and 6StringJazzer, Thank you so much for all of your responses and for providing me with solutions as well as practical options. I'm very appreciative of all of your help. Thanks, JL.