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...o&userid=36845
View this thread: http://www.excelforum.com/showthread...hreadid=565504

Try using the DATEVALUE() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"maverick_abhi" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
|
| 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...o&userid=36845
| View this thread: http://www.excelforum.com/showthread...hreadid=565504
|

Hello,

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


--
maverick_abhi
------------------------------------------------------------------------
maverick_abhi's Profile: http://www.excelforum.com/member.php...o&userid=36845
View this thread: http://www.excelforum.com/showthread...hreadid=565504

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

"maverick_abhi" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
|
| Hello,
|
| I tried the datevalue function. It does not work. Please
| Help.
|
|
| --
| maverick_abhi
| ------------------------------------------------------------------------
| maverick_abhi's Profile: http://www.excelforum.com/member.php...o&userid=36845
| View this thread: http://www.excelforum.com/showthread...hreadid=565504
|

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.


--
maverick_abhi
------------------------------------------------------------------------
maverick_abhi's Profile: http://www.excelforum.com/member.php...o&userid=36845
View this thread: http://www.excelforum.com/showthread...hreadid=565504

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

Vaya con Dios,
Chuck, CABGx3



"maverick_abhi" wrote:

>
> 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...o&userid=36845
> View this thread: http://www.excelforum.com/showthread...hreadid=565504
>
>

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

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

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

> =DATEVALUE(LEFT(A1,FIND(",",A1,1)-6)&",
> "&MID(A1,FIND(",",A1,1)-4,4))....formatted as you wish.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "maverick_abhi" wrote:
>
> >
> > 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...o&userid=36845
> > View this thread: http://www.excelforum.com/showthread...hreadid=565504
> >
> >

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.


--
maverick_abhi
------------------------------------------------------------------------
maverick_abhi's Profile: http://www.excelforum.com/member.php...o&userid=36845
View this thread: http://www.excelforum.com/showthread...hreadid=565504

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



"maverick_abhi" wrote:

>
> 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.
>
>
> --
> maverick_abhi
> ------------------------------------------------------------------------
> maverick_abhi's Profile: http://www.excelforum.com/member.php...o&userid=36845
> View this thread: http://www.excelforum.com/showthread...hreadid=565504
>
>