Covert text to time

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

Try using the DATEVALUE() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"maverick_abhi" <maverick_abhi.2blk30_1153991709.4402@excelforum-nospam.com> wrote in message
news:maverick_abhi.2blk30_1153991709.4402@excelforum-nospam.com...
|
| 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.

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" <maverick_abhi.2bll8p_1153993204.4252@excelforum-nospam.com> wrote in message
news:maverick_abhi.2bll8p_1153993204.4252@excelforum-nospam.com...
|
| 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.

=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.

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
>
>