• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get date from text string

atulrajratna

New Member
I have a long list of a text containing "mmm dd yyyy hh:mm:ss".


e.g.

May 21st 2013 21:42:04

Mar 22nd 2013 21:38:18

Dec 23rd 2013 21:37:15

Oct 1st 2013 21:35:48

Jan 1st 2013 21:32:37

Mar 3rd 2013 21:32:02

Apr 2nd 2013 21:29:51

Jun 20th 2013 21:28:19

Feb 21st 2013 21:25:30


I want to change it in dd-mmm-yyyy format. (time not required)


I trued it using left-find & mid formula but could not get proper date.


Please help me.

Thank you!


-Atul
 
Atul


Give this beauty a go:

=DATE(MID(A1,FIND(" ",A1,5)+1,4),MONTH(LEFT(A1,FIND(" ",A1)-1)&1),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,5)-FIND(" ",A1)-3))

Copy down

[pre]
Code:
May 21st 2013 21:42:04	21/05/2013
Mar 22nd 2013 21:38:18	22/03/2013
Dec 23rd 2013 21:37:15	23/12/2013
Oct 1st 2013 21:35:48	1/10/2013
Jan 1st 2013 21:32:37	1/01/2013
Mar 3rd 2013 21:32:02	3/03/2013
Apr 2nd 2013 21:29:51	2/04/2013
Jun 20th 2013 21:28:19	20/06/2013
Feb 21st 2013 21:25:30	21/02/2013
[/pre]
 
Hi Atul ,


Another possibility :


=VALUE(LEFT(REPLACE(A1,FIND("2013",A1)-3,2,","),LEN(A1)-9))


This will only work if all the dates are in 2013 , and all the times are in the format hh:mm:ss , so that removing the time is a matter of discarding the right-most 9 characters.


Narayan
 
Hi Atul,


This one too:


Code:
=DATE(RIGHT(A1,4),MID(A1,FIND("/",A1)+1,2),MID(A1,FIND("/",A1)-2,2))


Regards,
 
Back
Top