Time formats and adding.

[Guest]

I have a spread sheet that contains times (But they are in the general format.) These times are typically in Minutes and Seconds. Examples (MM:SS)- 2:22 55:25 7:40. Since these are in general format I can’t do any addition to the numbers because it is time. I can convert the General formats to (h):MM:SS and then the examples turn to Ex. 2:21:00 55:25:00 7:40:00 then I would divide by 60 to get the results I need and I am able to add the times and get the correct times. Now if you understand that is there an easier way to do this or how can I set up a macro to do this to an entire worksheet and keeping in mind that these times are spread out amongst different locations on a sheet and change position daily. I thought of doing a find and replace on a specific format and replace that format with the new one and then do a formula in the replace part. Is it possible to put a formula in the replace part using the value in the cell and dividing that value by 60 and replacing that cell with the new value? Any other suggest would be great! Thanks.

 

[Andy B]

Hi

There is nothing to stop you doing maths with times - they are numbers
behind the format! You can add them and subtract them just as you can with
'normal' numbers.
If you did want to divide a whole range by 60, one way would be to type 60
into a blank unused cell, select your range and Paste Special . . . Divide.
Make sure you back up your sheet first in case you get results you don't
want!

--
Andy.


"Trying to find the answer" <Trying to find the
(e-mail address removed)> wrote in message

I have a spread sheet that contains times (But they are in the general

format.) These times are typically in Minutes and Seconds. Examples
(MM:SS)- 2:22 55:25 7:40. Since these are in general format I can't do
any addition to the numbers because it is time. I can convert the General
formats to (h):MM:SS and then the examples turn to Ex. 2:21:00 55:25:00
7:40:00 then I would divide by 60 to get the results I need and I am able to
add the times and get the correct times. Now if you understand that is there
an easier way to do this or how can I set up a macro to do this to an entire
worksheet and keeping in mind that these times are spread out amongst
different locations on a sheet and change position daily. I thought of
doing a find and replace on a specific format and replace that format with
the new one and then do a formula in the replace part. Is it possible to put
a formula in the replace part using the value in the cell and dividing that
value by 60 and replacing that cell with the new value? Any other suggest
would be great! Thanks.

 

[Guest]

Thanks for the reply but this is more like a text than a number because of the ) in between the MM:SS. So when I try to add them I always get a 0.

Thanks again...

Chris

 

[Andy B]

Excel time has : in it. If you try
=ISNUMBER(A2)
and it returns TRUE, then it is Excel time

--
Andy.


"Trying to find the answer"

Thanks for the reply but this is more like a text than a number because of

the ) in between the MM:SS. So when I try to add them I always get a 0.

 

[Guest]

No it came back false plus this file is being imported from a phone system.
Thanks
Chris

 

[Andy B]

To make your data into Excel time, and to convert them into minutes:seconds
rather than hours:minutes, type 60 in a blank unused cell, select your range
and Paste Special . . . Divide. Format the cells as hh:mm as I said earlier.
Format the results as [mm]:ss
You can then treat them as numbers