Converting time :: Excell 2007

[Bob]

Hello, I am copying data from a table in a webpage and am having difficulty
in converting the minutes and seconds. Example

2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is.

I have tried [mm]:ss and that returns the real minutes as if calcualated in
hours.

Another example is 16:10 which is 16 minutes 10 seconds.

I need to convert these to example to the folowing format and am lost as to
how to do it..

2836.4 Minutes

and
16.17 minutes

I welcom all advise and help..

Thanks Bob

 

[Spiky]

Hello, I am copying data from a table in a webpage and am having difficulty
in converting the minutes and seconds.  Example

2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is..

I have tried [mm]:ss and that returns the real minutes as if calcualated in
hours.

Another example is 16:10 which is 16 minutes 10 seconds.

I need to convert these to example to the folowing format and am lost as to
how to do it..

2836.4 Minutes

and
16.17 minutes

I welcom all advise and help..

Thanks Bob

Are those original formats always exactly like that? What you have is
time displayed in text format, I assume. And you want to turn it into
a regular number value, not a "time" as Excel defines it. So this is
really just a "text-to-value" exercise.

Here's a formula that covers both of those formats you gave, assuming
that there are always 2 seconds digits:
=VALUE(LEFT(A1,SEARCH(":",A1,1)-1))+(VALUE(MID(A1,SEARCH
(":",A1,1)+1,2)/60))
=VALUE(LEFT(A1,SEARCH(":",A1,1)-1))+VALUE(RIGHT(A1,2)/60)

 

[Spiky]

Oops, I left an extra mess in that post. Ignore that second formula.
This is the one that should work:
=VALUE(LEFT(A1,SEARCH(":",A1,1)-1))+(VALUE(MID(A1,SEARCH
(":",A1,1)+1,2)/60))

 

[David Biddulph]

You've got 2836 hours 24 minutes and 0 seconds, so if you want to convert to
an Excel time of 2836 minutes 24 seconds you want to divide by 60.
Either use a formula =A2/60, or put 60 in a spare cell, copy it, select the
data you want to convert, and use Edit/ Paste Special/ Divide.

If you then want to convert to decimal minutes, you'd need to multiply by
24*60 and format the result as Number or General.

If you want to do the whole operation in one go it sounds as if you just
need to multiply by 24 and format the result as Number or General.

 

[T. Valko]

Try this:

=ROUND(A1/60*1440,2)

Format as General or Number

 

[Bob]

Thanks this works like a charm.. Bob

Try this:

=ROUND(A1/60*1440,2)

Format as General or Number

--
Biff
Microsoft Excel MVP

Hello, I am copying data from a table in a webpage and am having
difficulty
in converting the minutes and seconds. Example

2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00 is.

I have tried [mm]:ss and that returns the real minutes as if calcualated
in
hours.

Another example is 16:10 which is 16 minutes 10 seconds.

I need to convert these to example to the folowing format and am lost as
to
how to do it..

2836.4 Minutes

and
16.17 minutes

I welcom all advise and help..

Thanks Bob

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks this works like a charm.. Bob

Try this:

=ROUND(A1/60*1440,2)

Format as General or Number

--
Biff
Microsoft Excel MVP

Hello, I am copying data from a table in a webpage and am having
difficulty
in converting the minutes and seconds. Example

2836:24:00 is really 2836 minutes 24 seconds and who knows what the 00
is.

I have tried [mm]:ss and that returns the real minutes as if
calcualated
in
hours.

Another example is 16:10 which is 16 minutes 10 seconds.

I need to convert these to example to the folowing format and am lost
as
to
how to do it..

2836.4 Minutes

and
16.17 minutes

I welcom all advise and help..

Thanks Bob