CODEWITHSUNDEEP
exceltimecalculation
TUX One
TUX One

Reputation: 167

Excel adding up hours and minutes correctly

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

some more information: I know you can format the cell with the format of hours and minutes, but the problem that the values are generated by web-scraping. and if I format in hours and minutes the cell with for example the value 8:08 I have 00:28

Upvotes: 0

Views: 587

Answers (2)

Solar Mike
Solar Mike

Reputation: 8375

So, simple example:

enter image description here

Use A2+B2 or A4+B4, respectively.

I formatted as Time compared to the general above.

However, if your times go across midnight then you need to account for that by using C8-B8+(C8<B8) or MOD(C8-B8,1).

See this example:

enter image description here

Upvotes: 2

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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

enter image description here

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)

Upvotes: 2

Related Questions