I imported a column of min:sec data from Crystal reports XI. However when I
open in Excel, Excel has dropped the : and substituted a . Hence as an
example what should be 16:36 (min:sec) shows in Excel as 16.36

How can I convert something like 16.36 to min:sec?

And secondly, how could I convert 16.36 to a true minutes and hundreths of
a minute?

Any help appreciated!

Mike

With your number in a1, you can convert to a time with:

=time(0,int(a1),(a1-int(a1))*100)

To convert to a number of minutes, use:

=int(a1)+(a1-int(a1))*100/60

--
Regards,
Fred


"Sailonmike" wrote in message
...
I imported a column of min:sec data from Crystal reports XI. However when I
open in Excel, Excel has dropped the : and substituted a . Hence as an
example what should be 16:36 (min:sec) shows in Excel as 16.36

How can I convert something like 16.36 to min:sec?

And secondly, how could I convert 16.36 to a true minutes and hundreths of
a minute?

Any help appreciated!

Mike

Try this:

=TIME(0,LEFT(A1,2),RIGHT(A1,2))

Format as h:mm:ss


"Sailonmike" wrote:

I imported a column of min:sec data from Crystal reports XI. However when I
open in Excel, Excel has dropped the : and substituted a . Hence as an
example what should be 16:36 (min:sec) shows in Excel as 16.36

How can I convert something like 16.36 to min:sec?

And secondly, how could I convert 16.36 to a true minutes and hundreths of
a minute?

Any help appreciated!

Mike

This works! Thanks very much

Mike

"Teethless mama" wrote:

Try this:

=TIME(0,LEFT(A1,2),RIGHT(A1,2))

Format as h:mm:ss


"Sailonmike" wrote:

I imported a column of min:sec data from Crystal reports XI. However when I
open in Excel, Excel has dropped the : and substituted a . Hence as an
example what should be 16:36 (min:sec) shows in Excel as 16.36

How can I convert something like 16.36 to min:sec?

And secondly, how could I convert 16.36 to a true minutes and hundreths of
a minute?

Any help appreciated!

Mike

Both these work. Thank you. You are my hero!

Mike

"Fred Smith" wrote:

With your number in a1, you can convert to a time with:

=time(0,int(a1),(a1-int(a1))*100)

To convert to a number of minutes, use:

=int(a1)+(a1-int(a1))*100/60

--
Regards,
Fred


"Sailonmike" wrote in message
...
I imported a column of min:sec data from Crystal reports XI. However when I
open in Excel, Excel has dropped the : and substituted a . Hence as an
example what should be 16:36 (min:sec) shows in Excel as 16.36

How can I convert something like 16.36 to min:sec?

And secondly, how could I convert 16.36 to a true minutes and hundreths of
a minute?

Any help appreciated!

Mike

Looking a little further ..... with data of 13.0 this formula produced 13:13.

I used this from the previous post =TIME(0,INT(A1),(A1-INT(A1))*100) and
then formated as hh:mm:ss That gives 13:00

Mike

"Teethless mama" wrote:

Try this:

=TIME(0,LEFT(A1,2),RIGHT(A1,2))

Format as h:mm:ss


"Sailonmike" wrote:

I imported a column of min:sec data from Crystal reports XI. However when I
open in Excel, Excel has dropped the : and substituted a . Hence as an
example what should be 16:36 (min:sec) shows in Excel as 16.36

How can I convert something like 16.36 to min:sec?

And secondly, how could I convert 16.36 to a true minutes and hundreths of
a minute?

Any help appreciated!

Mike