Working with Dates in VBA

[mongoose]

I have a set of files that follow the following naming scheme:SOP_Audit-JV-006-02182019.docx

The last part of the file name is a date that I want to pull into VBA, which I have working fine using Split(). Is there something similar to this: https://www.php.net/manual/en/function.date.php in VBA? Something that would allow me to convert that string to a date and then be able to format it differently, or compare it with another date, break it down into just the month or year, etc.

I was reading about the CDate function and trying to use that but am having some issues with it.

Thank you!

[Fluff]

Maybe
Sub mongoose()
Dim St As String
Dim Dt As Date
St = Mid(ActiveCell, InStrRev(ActiveCell, "-") + 1, 8)

Dt = DateSerial(Right(St, 4), Left(St, 2), Mid(St, 3, 2))
End Sub

[mongoose]

Ya I just did it like so:


Dim auditDate: auditDate = CDate(DateSerial(Right(Left(Split(oFile.Name, "-")(3), 8), 4), _ Left(Left(Split(oFile.Name, "-")(3), 8), 2), _
Mid(Left(Split(oFile.Name, "-")(3), 8), 3, 2)))
What do you think?

[Fluff]

I'd assign the date string to a variable
Dim sp As Variant
Dim auditDate As Date

sp = Left(Split(oFile.Name, "-")(3), 8)
auditDate = CDate(DateSerial(Right(sp, 4), Left(sp, 2), Mid(sp, 3, 2)))

[p45cal]

fName = "SOP_Audit-JV-006-02182019.docx"
x = Left(fName, InStrRev(fName, ".") - 1) 'x contains everything before the last dot.
strDate = Mid(x, InStrRev(x, "-") + 1) 'strDate contains everything after the last - in x.
dd = DateSerial(Mid(strDate, 5), Left(strDate, 2), Mid(strDate, 3, 2)) 'dd contains the date.

edit postposting: Shucks, beaten to it.

[Paul_Hossler]

Ya I just did it like so:


Dim auditDate: auditDate = CDate(DateSerial(Right(Left(Split(oFile.Name, "-")(3), 8), 4), _ Left(Left(Split(oFile.Name, "-")(3), 8), 2), _
Mid(Left(Split(oFile.Name, "-")(3), 8), 3, 2)))
What do you think?


I'd Dim auditDate As Date