Covert text to time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maverick_abhi - 27 Jul 2006 10:10 GMT

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 |

+-------------------------------------------------------------------+

Signature

maverick_abhi

Reply to this Message

Niek Otten - 27 Jul 2006 10:23 GMT

Try using the DATEVALUE() function

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hey,
|
[quoted text clipped - 7 lines]
||Download: http://www.excelforum.com/attachment.php?postid=5112 |
| +-------------------------------------------------------------------+

Reply to this Message

maverick_abhi - 27 Jul 2006 10:35 GMT

Hello,

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

Signature

maverick_abhi

Reply to this Message

Niek Otten - 27 Jul 2006 12:03 GMT

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I tried the datevalue function. It does not work. Please
| Help.:confused: :confused:

Reply to this Message

maverick_abhi - 27 Jul 2006 12:51 GMT

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.

Signature

maverick_abhi

Reply to this Message

CLR - 27 Jul 2006 13:06 GMT

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

Vaya con Dios,
Chuck, CABGx3

> Hey,
>
[quoted text clipped - 7 lines]
> |Download: http://www.excelforum.com/attachment.php?postid=5112 |
> +-------------------------------------------------------------------+

Reply to this Message

CLR - 27 Jul 2006 13:22 GMT

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

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

Vaya con Dios,
Chuck, CABGx3

> =DATEVALUE(LEFT(A1,FIND(",",A1,1)-6)&",
> "&MID(A1,FIND(",",A1,1)-4,4))....formatted as you wish.
[quoted text clipped - 13 lines]
> > |Download: http://www.excelforum.com/attachment.php?postid=5112 |
> > +-------------------------------------------------------------------+

Reply to this Message

maverick_abhi - 28 Jul 2006 03:46 GMT

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.

Signature

maverick_abhi

Reply to this Message

CLR - 28 Jul 2006 13:25 GMT

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

> 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,
[quoted text clipped - 3 lines]
>
> Thanx again.

Reply to this Message

Rate this thread: