Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Converting Numbers to Time
Hello experts.
I have data as follows: 94423 94717 100523 101142 Is there a formula I could apply to change the numbers to time: 9:44:23 9:47:17 10:05:23 10:11:42 Thank you so much for your help. -- MB |
#2
|
|||
|
|||
One way:
=REPLACE(REPLACE(A1,LEN(A1)-1,,":"),LEN(A1)-3,,":")+0 Format the formula cells as time. HTH Jason Atlanta, GA -----Original Message----- Hello experts. I have data as follows: 94423 94717 100523 101142 Is there a formula I could apply to change the numbers to time: 9:44:23 9:47:17 10:05:23 10:11:42 Thank you so much for your help. -- MB . |
#3
|
|||
|
|||
=LEFT(A1,IF(A1<100000,1,2))&":"&MID(A1,IF(A1<10000 0,2,3),2)
&":"&RIGHT(A1,2) If you put all that inside of =value() it will convert to to the excel time code, but unless you are using military time these will all be AM. -----Original Message----- Hello experts. I have data as follows: 94423 94717 100523 101142 Is there a formula I could apply to change the numbers to time: 9:44:23 9:47:17 10:05:23 10:11:42 Thank you so much for your help. -- MB . |
#4
|
|||
|
|||
=TEXT(TEXT(A2,"000000"),"00\:00\:00")+0
where A2 houses a value like 94423. Custom format the formula cell as: h:mm:ss MB wrote: Hello experts. I have data as follows: 94423 94717 100523 101142 Is there a formula I could apply to change the numbers to time: 9:44:23 9:47:17 10:05:23 10:11:42 Thank you so much for your help. |
#5
|
|||
|
|||
On Sat, 19 Feb 2005 00:07:26 +0100, Aladin Akyurek wrote:
=TEXT(TEXT(A2,"000000"),"00\:00\:00")+0 where A2 houses a value like 94423. Custom format the formula cell as: h:mm:ss I like that approach! But even simpler: =--TEXT(A1,"00\:00\:00") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
roundoff when converting text to numbers | Excel Worksheet Functions | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Can't stop time duration [hh]:mm:ss converting to time of day | Excel Discussion (Misc queries) | |||
Converting time to numbers | Excel Discussion (Misc queries) |