[VBA] Convert a String to Date Solved

Question

Hello,

I suppose it's not something complicated, but I must admit I'm stuck...

Let me explain, I have a string input like "dec2013", "jan2013", etc...
and I would like to retrieve the date (first of the month) in the format DD/MM/YYYY.
I tried format(myDate, "DD/MM/YYYY"), but it didn’t work... when I hover over it, the string still contains a string...

I hope I've been clear? :-)

Configuration: VGN-cs21S, Windows 7 Professional 64bits

--
Google is your friend!

PHILOU10120 · Answer

Hello

With a formula

In A4 the information to be processed "janv2013" or "déc2013"

=CNUM(LEFT(A4,3)&"/"&RIGHT(A4,4)) format the cell dd/mm/yyyy

It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from their mistakes.

eriiic · Answer

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.

Miura564 · Answer

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;@")

michel_m · Answer

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

yacleouf · Answer

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!

[VBA] Convert a String to Date

5 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player