Adding Times of different formats together

[Mattias1]

Hello,

I have come across a problem with my excel sheet. I am trying to sum values which represent a duration. In the past I have used the decimal system which was straightforward, however now I have to switch to the serial system (2:30 instead of 2.5). The numbers which were previously entered as decimal will stay in that format, but I am having trouble adding the serial values into the decimal system. Is there a way I can sum the values together? The end result would preferably be in decimal, but can be anything.

Thanks!

ie:


5.7
1.8
1.9
4:25
2:10
1:09

SUM=17.1

[Glenn Kennedy]

Are the times (before addition) always < 24 hours? If so, try this: =IF(A1>=1,A1/24,A1)

[Mattias1]

Yes, the times before addition are always going to be under 24 hours.

I am looking for a formula for the cell with the Sum in order to combine all the values of both decimal and serial into one.

So in this example, I am looking for the formula to use in A8 which would SUM(A1:A6) using both serial and decimal values. I do not wish to change the serial into decimal in the data itself.

A1 5.7
A2 1.8
A3 1.9
A4 4:25
A5 2:10
A6 1:09
A7 -------
A8 17.1