How do I add up hours and minutes?

[Guest]

...........If they are separated by a decimal.

I have tried 'Replace all' to change the'.' to ':' but it changes the
numbers!!

eg it changes '0.10' to '0:01' and '9.50' to '9:05'

I have got a vast amount of dumped in data and can't possibly change them
all manually.

 

[Guest]

Hi,
Try:
divide this result by 24 and format the resulting cell with the custom
format: [hh]:mm:ss

 

[Ron Rosenfeld]

..........If they are separated by a decimal.

I have tried 'Replace all' to change the'.' to ':' but it changes the
numbers!!

eg it changes '0.10' to '0:01' and '9.50' to '9:05'

I have got a vast amount of dumped in data and can't possibly change them
all manually.

Create a "helper" column.

If your data is in F1:Fn put this formula in your helper column row 1:

=TIME(INT(F1),MOD(F1,1)*100,)

and copy/drag down as necessary.

Format the result as [h]:mm

Then copy the helper column, and Paste Special/Values back over your original
data in column F.

!!Be sure to make a backup of your data first!!


--ron

 

[Guest]

Hi
What i done was to use seperate calculations for hours and min but to hide
the calculations to keep my rosta neat and tidy.
e.g
=HOUR(a6-a6)
=MINUTE(a6-a6)

for more info dont hesitate to contact me