Converting time values in Excel 4

[JamesMichell]

I have columns of times in minutes and seconds and decimals of seconds e.g. 1:39.235
I need to show all these times in seconds and decimals of seconds e.g. 99.235 but formatted as a normal number. Is there a way of doing this?

 

[bluedragon2]

What is the format of the time?

Is it formated as time or text?

Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

I have the Time value in A2

If it is text:

=VALUE(LEFT(A2,FIND(":",A2)-1))*60+VALUE(MID(A2,FIND(":",A2)+1,FIND(".",A2)-FIND(":",A2)-1))+VALUE(RIGHT(A2,FIND(".",A2)))

If it is a time value:

Format the target cell as a number with three decimals
=86400*VALUE(A2)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[JamesMichell]

Well, its Time, but in asking the question you've given me a clue. I could import the figures as text in which case one could take out the leading figure and then add 60 to the remainder. But at the moment it is in a Time format and it would be really useful to know how to convert it.

Thanks for your help

 

[Bowers74]

Instead of going through all of the trouble of changing your import routine, there are three possible methods that you could use:

1. If you only want to "see" the values as 99.235 then you only have to change the cells number format to the Custom Format [ss].000.

2. If you want to have the values displayed in another cell, there are two other ways you could do this:

a. Formula =TEXT(A1,"[ss].000&quot in cell B1 will return the time in the format you want, but only as text (i.e. can not be used in any calculations)

b. Formula =VALUE(TEXT(A1,"[ss].000&quot) will return the value in the format you want, and it can be used in calculations.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JamesMichell]

Blue, Sorry my last post crossed with your last post!
Yes, with the time format that works fine and I understand the principal now, in that there are 86400 seconds in 24hrs. So if I was converting to minutes I would multiply by 1440........

That's great..thanks very much indeed.

 

[JamesMichell]

Mike,
More crossing posts...........! And your solutions open up a whole new range of possibilities. Thanks a lot.

James

 

[ladyck3]

Mike, you opener of new ranges of possibilities... darn too bad I was so late, I was going to suggest a custom format...

Well, ever further up the list ya go

Good shot!



LadyCK3
(aka Laurie)
<fancy Signature Here>
(Just waiting til I think of something catchy, but I wanted a signatue TOO)

 

[DaleWatson123321]

Mike,

Your formulas look familiar - from some time ago, but it's good to be reminded. (I haven't been working with Excel as much recently so I'm probably getting &quot;rusty&quot; (forgetful is perhaps more accurate) ;-)

Thanks again, and another STAR for you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Bowers74]

Thanks to all of you:

I use the a.m. formulae a lot at work! And no one there understands them. They're just happy that their spreadsheets do what they want.

You can use almost any custom format within the TEXT() function. I mostly use:

=TEXT(A1,&quot;#,##0.00,&quot to return the thousandth of a number.

or

=TEXT(A1,&quot;#,##0.00,,&quot to return the millionth of a number.

I should write a FAQ on this too! What do y'all think?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ladyck3]

FAQ?? Go for it!!

<getting closer> <wink>



LadyCK3
(aka Laurie)
<fancy Signature Here>
(Just waiting til I think of something catchy, but I wanted a signatue TOO)

 

[DeLaMartre]

Well done, Mike; Another star for your expertise. I think a FAQ on this topic would be great!

Regards to all,

-Bob in California