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
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
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
>.
>
=LEFT(A1,IF(A1<100000,1,2))&":"&MID(A1,IF(A1<100000,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
>.
>
=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.
>
>
On Sat, 19 Feb 2005 00:07:26 +0100, Aladin Akyurek <[email protected]> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks