Formatting times taken from webpages
Posted by Frode Klow on December 12, 2001 4:50 PM
I've used web query to import a table of times into Excel. The times are written like this: 0:29, 1:20, 4:36, etc... where the number before the colon is minutes, and the number after is seconds. My problem is that when Excel reads this, it recognizes the number before the colon as hours and the number after as minutes. I've tried formatting the cells before importing the numbers, I've tried using all kinds of formats after, so what am I doing wrong?
Can anyone help, please?
Posted by Mark W. on December 12, 2001 5:18 PM
You could convert them back to m:ss using...
=TEXT("0:"&TEXT(A1,"[h]:mm"),"h:m:ss")+0
...where A1 contains your misinterped time value.
Posted by Mark W. on December 12, 2001 5:20 PM
Wish I could spell... ; )
You could convert them back to m:ss using...
=TEXT("0:"&TEXT(A1,"[h]:mm"),"h:m:ss")+0
...where A1 contains your misinterpreted time value.
Posted by Mark W. on December 12, 2001 5:27 PM
One more piece of advice...
The cell containing this formula should be formatted
as: [m]:ss
This should accommodate times such as 125:59. You could convert them back to m:ss using... =TEXT("0:"&TEXT(A1,"[h]:mm"),"h:m:ss")+0 ...where A1 contains your misinterpreted time value. :
Posted by Frode Klow on December 12, 2001 5:36 PM
Thank you, it worked! (NT)
Posted by Mark W. on December 12, 2001 6:38 PM
New, Improved Formulation!
Sitting in traffic on the way home I realized
that I had over-engineered this formula, and
the outer-most TEXT function isn't necessary.
Try this instead...
=("0:"&TEXT(A1,"[h]:mm"))+0
...it should produce the same results!
: The cell containing this formula should be formatted : as: [m]:ss : This should accommodate times such as 125:59.
Posted by Juan Pablo G. on December 12, 2001 6:54 PM
Off Note - Mark, did you...
read my request ? i don't have your mail... are you not interested in this ? or just missed the post ?
If so, please, mail me to and i'll email you explaining what we're doing here.
Juan Pablo G.
JuanPablo@MrExcel.com Sitting in traffic on the way home I realized that I had over-engineered this formula, and the outer-most TEXT function isn't necessary. Try this instead... =("0:"&TEXT(A1,"[h]:mm"))+0 ...it should produce the same results! :
Posted by Frode Klow on December 12, 2001 7:00 PM
Re: New, Improved Formulation!
Hehe, what a classic...
The formula worked without the first TEXT, but I'm not able to do a search and replace (I get a formula error, because I only want to replace part of it due to cell references) so I'll just go for the old one. But thanks anyway!
Sitting in traffic on the way home I realized that I had over-engineered this formula, and the outer-most TEXT function isn't necessary. Try this instead... =("0:"&TEXT(A1,"[h]:mm"))+0 ...it should produce the same results! :
Posted by Mark W. on December 13, 2001 7:14 AM
Re: Off Note - Mark, did you...
Yeah, I must of missed your posting...
sending my email address. read my request ? i don't have your mail... are you not interested in this ? or just missed the post ? If so, please, mail me to and i'll email you explaining what we're doing here. Juan Pablo G. JuanPablo@MrExcel.com : Sitting in traffic on the way home I realized : that I had over-engineered this formula, and : the outer-most TEXT function isn't necessary. : Try this instead... : =("0:"&TEXT(A1,"[h]:mm"))+0 : ...it should produce the same results! :
