Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 14 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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