[VBA] Convert a String to Date

Hello,

or select the column or range and 'data / convert...', choose Date YMD in step 3

eric

--
You will never respond to an unsolicited DM...
Well, that's done.

Good evening,

your problem can be solved with a simple formula. Assuming that your character strings are in column A. The formula is:

=TEXT(DATEVALUE(LEFT(A1,LEN(A1)-4)"/"&RIGHT(A1,4));"dd/mm/yyyy;@")

Hello

If I write janv2013 in a cell, Excel automatically converts janv2013 into a date

but if I have a variable in code like "janv2014", sending it to a cell does not produce the conversion into a date

so if we are in code as yacleouf seems to indicate

Function transfo_date(date_txt As String) As Date Dim anne As Integer, mois_txt As String, mois(), mois_num As Byte annee = Right(date_txt, 4) mois_txt = Left(date_txt, Len(date_txt) - 4) mois = Array("janv", "fév", "mars", "avr", "mai", "jun", "juil", "aout", "sept", "oct", "nov", "déc") For cptr = 0 To 11 If mois(cptr) = mois_txt Then mois_num = cptr + 1 Exit For Next transfo_date = DateSerial(annee, mois_num, 1) End Function Sub test() Range("B2") = transfo_date("fév2014") End Sub 

please check my spelling of the months in the array!!!

Michel

Wow, so many responses!
Thank you all for your contributions!
Formula solutions are not feasible in my case (VB code) and I don't want to use formulas or anything else :-)

By the way, to solve my problem (through a formula!), a simple

=DATEVALUE(A1)

is more than enough and gives me my expected result,

what I would like is to find a solution as "simple" and "quick" as this but through VB code!?

Thanks again,

@michel_m, could you explain to me how you would modify your "month" table if the data for this table is contained in an Excel range (for example: A1:A12)?

Thank you :-)

--
Google is your friend!