I would like to sum up values containing hours and minutes, these are not in decimal format, but hours and minutes, the problem is that if I want to sum them up the result is not correct. wrong values are displayed with formatting in excel

A1 11.35 9.55 = 20.90

CodePudding user response：

So, simple example:

I formatted as Time compared to the general above.

However, if your times go across midnight then you need to account for that.

See this example:

CodePudding user response：

If you are trying to get the end Time , then I would recommend formatting the cells to time format like solar mike suggested.

However, if you actually do want to calculate the amount of hrs and minutes like 18.55 13.45 = 32 hrs and 40 min then you can use a formula to add the hours and then convert the sum of everything after the decimal place to hrs

The formula I came up with is this: =TRUNC(A1) TRUNC(B1) IF(A1 B1-(TRUNC(A1) TRUNC(B1))>0.6,1 A1 B1-(TRUNC(A1) TRUNC(B1))-0.6,(A1 B1-(TRUNC(A1) TRUNC(B1))))

For 18.55 13.45 It essentially does the following :

Sum up the whole numbers 18 13. Then it convert .55 .45 to 1.4 by adding an hr to the whole number total and subtracting an hr (.6) from the decimal place. If the number after the decimals add up to less than 60, then it returns the remainder as is.

CodePudding user response：

This worked for me, but please, notice my decimal separator is , not the dot, so you'll need to change this part:

Formula in B3 is:

=VALUE(SUBSTITUTE(TEXT(B2;"@");",";":"))

What I do is convert the number to text with TEXT(B2;"@"). Now, because it's a text, you can replace chars, so I replace the comma with the : using SUBSTITUTE(TEXT(B2;"@");",";":") and at the end we use VALUE to get the decimal value of that time.

The you can sum up normally and just apply time format to sum cell (21:30 in my image)