Covert text to time

[maverick_abhi]

Hey,

I am unable to convert a piece of text to the time format. I am copying
this from an external source. Example, "July 24 2006, 05:31 PM" Excel
does not convert it to the date format. Please help. Please see
attached file


+-------------------------------------------------------------------+
|Filename: TTT.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5112 |
+-------------------------------------------------------------------+

 

[Niek Otten]

Try using the DATEVALUE() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Hey,
|
| I am unable to convert a piece of text to the time format. I am copying
| this from an external source. Example, "July 24 2006, 05:31 PM" Excel
| does not convert it to the date format. Please help. Please see
| attached file
|
|
| +-------------------------------------------------------------------+
||Filename: TTT.zip |
||Download: http://www.excelforum.com/attachment.php?postid=5112 |
| +-------------------------------------------------------------------+
|
| --
| maverick_abhi
| ------------------------------------------------------------------------
| maverick_abhi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36845
| View this thread: http://www.excelforum.com/showthread.php?threadid=565504
|

 

[maverick_abhi]

Hello,

I tried the datevalue function. It does not work. Please
Help.

 

[Niek Otten]

What does "does not work" mean? Error value? Which one? Wrong date? which one? Number? what? etc. What is your windows date
format?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Hello,
|
| I tried the datevalue function. It does not work. Please
| Help.
|
|
| --
| maverick_abhi
| ------------------------------------------------------------------------
| maverick_abhi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36845
| View this thread: http://www.excelforum.com/showthread.php?threadid=565504
|

 

[maverick_abhi]

When i use the datevalue formula it gives the "VALUE" error. My Windows
Date format is "Thursday, July 27, 2006." and the data that I am
copying in Excel is "July 26 2006, 07:26 AM" Please look at the excel
file attached.

Thanx for any help u can provide.

 

[CLR]

=DATEVALUE(LEFT(A1,FIND(",",A1,1)-6)&",
"&MID(A1,FIND(",",A1,1)-4,4))....formatted as you wish.

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

And, if you want just the time.........

=MID(A1,FIND(",",A1,1)+3,8)*1......formatted as you wish

Vaya con Dios,
Chuck, CABGx3

 

[maverick_abhi]

You are a genius. Thanx So much for all the help. I finally was able to
get what I needed. The final formula looks like,
"DATEVALUE(LEFT(A1,FIND(",",A1,1)-6)&","&MID(A1,FIND(",",A1,1)-5,5))&VALUE(RIGHT(A1,8))"

If in case I require any further help, I know you r there.

Thanx again.

 

[CLR]

You're welcome............maybe it's a typo, but I get better results with

=DATEVALUE(LEFT(A1,FIND(",",A1,1)-6)&","&MID(A1,FIND(",",A1,1)-5,5))+VALUE(RIGHT(A1,8))

instead of

=DATEVALUE(LEFT(A1,FIND(",",A1,1)-6)&","&MID(A1,FIND(",",A1,1)-5,5))&VALUE(RIGHT(A1,8))

I'm using XL97SP2 on WinXP

Vaya con Dios,
Chuck, CABGx3