String to Date Conversion (VBA)
Solvedccm81 Posted messages 11033 Status Membre -
Hello,
I have an Excel column "due date" whose value is presented in the form ddMM, such as 101 for 01/01, 2412 for 24/12, etc...
I want to convert this into the current year's date (without necessarily displaying it), in order to make a calculation relative to today's date.
However, I am struggling with the conversion. Could you help me? Thank you.
Details:
The goal is to convert this due date into a date so that I can then automatically filter, upon opening, the rows with a due date within 30 days from today's date.
A direct CDate or DateFormat function doesn't work (it calculates 10/04/1900 for the due date 101), so I thought of something like this, but it doesn't work (not to mention that the Left function would often be incorrect):
Sub Auto_Open() For c = 7 To Cells(Rows.Count, 2).End(xlUp).Row D = Right(c, 2) E = Left(c, 2) CDate(Range(c) = E & "/" & D) .... Next c End Sub
2 réponses
Hello
Try this, your "date" being of integer type
Public Function cvdate(d As Integer) As Date Dim j As Long, m As Long, a As Long, cvd As String a = Year(Date) m = d Mod 100 j = d \ 100 cvd = CDate(j & "/" & m & "/" & a) cvdate = cvd End Function
or this, the date being of text type
Public Function cvsdate(d As String) As Date Dim j As Long, m As Long, a As Long, cvd As String a = Year(Date) m = Right(d, 2) j = Left(d, Len(d) - 2) cvd = CDate(j & "/" & m & "/" & a) cvsdate = cvd End Function
Best regards