 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Time formatting hh:mm:ss
Hi, all, I have data downloaded from a source that provides the time as 5-6 digits. E.g., 94423, 132218, etc. I want to show it as hh:mm:ss. So: 09:44:23, 13:22:18, etc. I've tried various custom-formatting things but can't get it to come out right. Can it be done simply with formatting, or do I need to resort to a formula? Help sincerely appreciated. By the way, for doing it via formula I found this: =SUM(MID(A1,{1,3},2)/{24,1440}) I found that here: http://www.mrexcel.com/archive/Formulas/4699.html I like it, but the problem with applying it to my case is (a) my data includes seconds; and (b) my data is not always 6 digits long: there is not a leading zero in what I download from the source. Thanks, Dallman For a custom format, use: 00\:00\:00 To convert to a real Excel time, use: =time(int(a1/10000),mod(int(a1/100),100),mod(a1,100)) Regards, Fred. > Hi, all, > [quoted text clipped - 19 lines] > Thanks, > Dallman Excellent on both counts! Thank you, Fred. I didn't see before that ":" needs to be quoted in the custom format. And that formula's the cat's meow! Dallman -------------------------------------- > For a custom format, use: > [quoted text clipped - 28 lines] > > > > Thanks, Dallman You can also convert with this formula =TEXT(A1,"00\:00\:00")+0 format result cell as hh:mm:ss > Excellent on both counts! Thank you, Fred. > I didn't see before that ":" needs to be quoted [quoted text clipped - 36 lines] > > > > > > Thanks, Dallman Very nice! I don't follow the +0 at the end, though. I guess you are expecting it to cause some sort of data coersion? But when I leave the +0 off, the formula seems to work equally well. Dallman -------------------------------------- > You can also convert with this formula > [quoted text clipped - 42 lines] > > > > > > > > Thanks, Dallman Without the +0, the value returned from the formula is a string. It'll look like a time. With the +0, you'll see a number. And if you apply the nice number format, it'll actually be a time. > Very nice! > [quoted text clipped - 53 lines] > > > > > > > > > > Thanks, Dallman  SignatureDave Peterson Ah, I see. Of course. Thank you, Dave. I do believe that it works because of what the CS folks would call "type-casting" or "coercion," unless I am mistaken. In any case, very nice soloution from DaddyLongLegs. Dallman > Without the +0, the value returned from the formula is a string. It'll look > like a time. [quoted text clipped - 18 lines] > > > > > > format result cell as hh:mm:ss |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.