Convert date text to Excel date

**kborgers** · 06-19-2012, 02:54 AM

I have a list of dates in my spreadsheet that were copied (from an html file) and pasted into it. Therefore,this information is text. I would have thought it simple enough to convert this to excel date format (ie. numeric) that I can then manipulate as much as my heart desires! But no. I've tried, searched and cannot find a solution and I have a suspicion (hope) that someone here will tell me that it's quite simple? The date text as an example is: "Saturday 18 August" (without the quotes of course). I tried adding '2012' to these dates and then attempted to convert and that didn't work either.

**outofthehat** · 06-19-2012, 03:10 AM

Put the 12 months of the year in a range somewhere else, and name it Months.
Then, if the date's in A2, B2 can be
=DATE(2012,MATCH(RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2)+1)),Months,0),MID(A2,FIND(" ",A2)+1,2))

**tom1977** · 06-19-2012, 03:10 AM

Hi could You upload sample file (excel file)?

**outofthehat** · 06-19-2012, 03:12 AM

To explain, this builds the date from year (2012), month (match the month name in the range of months to give its month number - the name is extracted by taking the end of text string after the second space), day (take the middle of the text string, the 2 characters after the first space)

**dilipandey** · 06-19-2012, 03:12 AM

Hi Kborgers,

If you have the date in cell A1, you can use below formula:-

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))*1

and then convert the format of the cell as Date

Regards,
DILIPandey

<click on below 'star' if this helps>

**kborgers** · 06-19-2012, 03:36 AM

I thought I already posted a reply to you all but I cannot see it so I'll try again! Thanks to all for quick response. I went with Dilip's solution as it worked beautifully and was (embarassingly) simple

Thanks again all.

**dilipandey** · 06-19-2012, 01:35 PM

You are welcome Kborgers..

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

Convert date text to Excel date

LinkBack

Thread Tools

Rate This Thread

Display

Convert date text to Excel date

Re: Convert date text to Excel date

Re: Convert date text to Excel date

Re: Convert date text to Excel date

Re: Convert date text to Excel date

Re: Convert date text to Excel date

Re: Convert date text to Excel date

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions