1

I've got an Excel 2010 spreadsheet that has a large number of dates for the year 2014 entered in the following format: Thursday 24th July that I need to convert into standard date time format (the end result is going to be imported into an SQL table and the dates will be like YYYYMMDD so the date I mentioned above would need to be 20140724, I can deal with that conversion, but Excel won't recognise Thursday 24th July as a date).

1

OK, so this is a pretty ugly formula, and I'm sure someone can come up with one much cleaner. Still I've tested it and it works, which I guess is what matters.

In an adjacent column enter this formula:

Where the dates start in A2

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  RIGHT($A2,LEN($A2)-FIND(" ",$A2)),"th","",1),"rd","",1),"st","",1"),"nd","",1))


DATEVALUE converts a date stored as text into an Excel date code, so that it can be formatted correctly, but the date Thursday 24th July has a few issues with it;

  1. DATEVALUE can't make any use of the day of the week, and it's not relevant anyway
  2. the th or rd at the end of the day numbers are also values that the formula can't use
  3. There's no year stored in this date

This formula can't do anything about the year, but what it does do is this (working from the middle out):


RIGHT($A2,LEN($A2)-FIND(" ",$A2))

Return the day number and month by finding out how long the text date is and subtracting the number of characters between the start and the first space (the space after the day name)

SUBSTITUTE([FindText],"th","")

Replace any instance of th in the returned string with nothing

SUBSTITUTE([FirstSubstitute],"rd","")

Replace any instance of rd in the returned string with nothing

=DATEVALUE([StringReplacers])

Convert the result to a date. With no year supplied it will assume the current year.

  • Looks good, would probably need to do SUBSTITUTE([SecondSubstitute],"nd","") and similar for st, but but would the st interfere with August? – laurencemadill Dec 31 '13 at 10:34
  • 1
    Sorry about that, rushing a bit :( I've updated the answer to include all sufixes (just remove the caridage return, it's just easier to copy this way) I'd have thought August would be an issue but it's working fine. Still to be on the safe side I've added a First Instance restriction so it will stop substituting after it hits the suffix – CLockeWork Dec 31 '13 at 10:47
  • 1
    cool "st",",1" should be "st","",1 I think. It won't let me edit less than 6 characters! – laurencemadill Dec 31 '13 at 11:43
  • 1
    No worries, done :) – CLockeWork Dec 31 '13 at 11:51
0

I found one solution to this while writing the question, so I thought I'd submit it anyway in case it helps anyone else:

It's not an ideal solution, however it appears to work. A number of batch Find/Replace actions to convert the dates to what Excel will understand. Important to note the spacing, do the searches excluding the apostrophes but note if there is a space within them

  1. Find/Replace, find 'Monday ' and replace with '', same for 'Tuesday ', 'Wednesday ' etc to remove the days from the date.
  2. Find/replace, find 'January' and replace with 'January 2014', same for 'February', 'March' etc to add the year that you want
  3. Find/replace, find 'st ' and replace with ' ', same for 'nd ', 'rd ' and 'th '

Then you can format cells as date and Excel should recognise and convert them. The correct spacing in the search/replace is important because you don't want to find 'st' for '1st' and have it remove the end of 'August'

  • Note that I added the apostrophes above because the code tags drop trailing spaces, remember to exclude the apostrophes – laurencemadill Dec 31 '13 at 9:32
0

Well, you can use something a little shorter:

=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
  MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100))*1

The first part:

IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))

Extracts the date. It tries to extract 2 characters after the first space first, and if that's a number (doesn't return an error when multiplied by 1), adds it to the second part which is the month.

If that's an error (e.g. first two characters in 1st are 1s which is an invalid number), then that's a date less than 10 and thus extract only one character.

MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)

Extracts the month with its preceding space.

Then multiply the whole thing by 1 to get the datevalue. You can format as yyyymmdd via custom formatting and the dates will be ready to be imported to SQL.

As with @CLockeWork's formula, since no year is mentioned in the formula, the sheet will take the current year. If you need to specify it, you'll have to do it manually, for example, like this:

=(IFERROR(MID(K32,FIND(" ",K32)+1,2)*1,MID(K32,FIND(" ",K32)+1,1))&
  MID(K32,FIND(" ",SUBSTITUTE(K32," ","",1))+1,100)&" 2013")*1

For the year 2013.

0

You can use this formula to convert to a date in 2014

=(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),"")&2014)+0

that will return the date serial number, format in required date format

The first FIND function finds the first space at character 11 or later (which must be the second space in the string given the length of weekdays), and then, based on that, REPLACE removes the two characters immediately before (which will be the "st","th","rd" or "nd")

...and then the 2nd FIND finds the first space and REPLACE replaces all the text before that, leaving you with just 24July or similar

You can then concatenate the year [2014] to the end and use +0 to co-erce to a date

If you want to go directly to 20140724 (as a string) you can use this version

=2014&TEXT(REPLACE(REPLACE(A2,FIND(" ",A2,11)-2,2,""),1,FIND(" ",A2),""),"mmdd")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.