Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a column of date-like string values in the format yyyy-mm-dd, such as 2011-Sep-13. I need to convert these to Excel date serial numbers so that I can use them in formulas.

DATEVALUE isn't able to recognize this format; I just get #VALUE! when I try. It also does not take a custom date format (unlike most programming languages).

How can I convert arbitrary date formats into Excel date values?

share|improve this question
up vote 17 down vote accepted

You'll need to do string parsing, then pass an appropriate formatted value into DATEVALUE – something like this:

=DATEVALUE(RIGHT(A1,2)&"-"&MID(A1,6,3)&"-"&LEFT(A1,4))

(edit: updated formula - tested and it works for me on one example - may need to refine depending on your input)

share|improve this answer
1  
+1: I had this on the clipboard ready to go when you posted: =DATEVALUE(RIGHT(A1,2)&MID(A1,FIND("-",A1),5)&MID(A1,3,2)) – Excellll Sep 13 '11 at 18:16
    
Heh, I never thought of just rearranging the bits. Thanks! – Craig Walker Sep 13 '11 at 18:32

Note that month names are Windows Regional Options specific. So, if your Windows Regional Options are set to Ukrainian, then DATEVALUE would return #VALUE! for "07-Nov-2012", but would accept "07-Лис-2012".

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .