How do I add up hours and minutes?

F

Flum

...........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.
 
M

mzehr

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

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.
Click to expand...

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
 
G

ggozzy

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
 
F

Flum

Thank you all for your help. None of those worked unfortunately.

It just seems everytime I change the format in the column it destroys the
data and changes all the numbers!!

Perhaps I am being dim.

Would still love more advice if you have any to
spare.............................
 
Top