Microsoft Excel - reformat text to a date - Asked By Cherifa Hima on 30-Jul-13 01:24 PM

Hi,

I need an excel formula that picks the date from the text and split it into two dates: start and end date in two different cells. e.g.
 August 19 - November 25, 2013 will become 08/19/2013  in one cell and 11/25/2013 in another cell.
Harry Boughen replied to Cherifa Hima on 30-Jul-13 06:31 PM
Hello Cherifa,
Assuming your text is in A6, the following two formulae will do it.

=DATEVALUE(MID(MID(MID(SUBSTITUTE(A6," ","^",1),1,256),FIND("^",SUBSTITUTE(A6," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",1),1,256),FIND("^",SUBSTITUTE(A6," ","^",1)),256))-2)&"/"&MONTH(DATEVALUE(LEFT(A6,FIND(" ",A6)-1)&" 1"))&"/"&MID(SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))),FIND("^",SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))))+1,256))

=DATEVALUE(LEFT(RIGHT(A6,8),2)&"/"&MONTH(DATEVALUE(MID(MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256))-2)&" 1"))&"/"&MID(SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))),FIND("^",SUBSTITUTE(A6," ","^",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))))+1,256))

You might have to switch the day and month extraction bits around depending on the set up of your computer date system and you will have to format the cells to get the date in your preferred format.

Regards
Harry
Cherifa Hima replied to Harry Boughen on 31-Jul-13 09:13 AM
Thanks. The second formula is giving me #VALUE! and the first one does not work for all cases ( see examples below). I cannot change the extraction of the date format. It is like that in the marketing database.
 July 11 - December 22, 2013 July 20 - August 31, 2013 November 1 - December 20, 2013 September 1 - October 31, 2013 July 27 - October 31, 2013 November 2 - December 20, 2013 August 16 - September 30, 2013 January 4 - January 31, 2014 November 1 - December 20, 2013 September 1 - October 31, 2013 September 8 - September 30, 2013 August 24 - September 7, 2013 November 1 - December 18, 2013 July 28 - October 31, 2013 July 1 - December 20, 2013 August 20 - October 31, 2013
Harry Boughen replied to Cherifa Hima on 31-Jul-13 05:48 PM
Hello Cherifa,

cherifa1.zip

Your data works absolutely fine.  Perhaps there was an error in copying the formulae or you need to switch the day and month sections of both if you are using a computer set for american format dates.
Regards
Harry
Cherifa Hima replied to Harry Boughen on 01-Aug-13 06:41 PM
Thanks.
I am using excel 2010. When I open your file I see that the formula is working but when I enable editing I see #value! in most of the cells.

Harry Boughen replied to Cherifa Hima on 01-Aug-13 07:08 PM
Hello Cherifa,
Here is a file from 2010 with the formulae in both date formats.  Hopefully one of them will be OK for you.
cherifa2.zip
Regards
Harry
Cherifa Hima replied to Harry Boughen on 01-Aug-13 07:55 PM
Thanks it is wroking now.