up vote 3 down vote favorite
share [g+] share [fb]

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?

link|improve this question
62% accept rate
feedback

1 Answer

up vote 6 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)

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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