CODEWITHSUNDEEP
exceldate-formatvba
Sam Stephenson
Sam Stephenson

Reputation: 5190

Convert string variable to date

I need to convert a string variable to a date variable in a VBA macro, to process the date to get the mMonth and year in order to name the worksheet

Call GetMonthandYear(MonthYear, FileDate)
    ActiveSheet.Name = MonthYear

I looking to create a method called GetMonthandYear. FileDate is a String in a date format, dd.MM.yyyy HH-mm-ss. If I can change the variable to a date, I can change the format to MMMM/yyyy and then use ToString, I think, and assign it to MonthYear.

Is there a way to change the string to a date?

Upvotes: 1

Views: 28302

Answers (3)

Alexander Burov
Alexander Burov

Reputation: 1322

You can get Date using CDate function but it requires - to be replaced by : and . to be replaced by /.

Dim yourStringDate, sBuf As String
Dim yourDateVariable As Date

yourStringDate = "15.04.2012 16-31-18"
sBuf = Replace$(yourStringDate,"-", ":")
sBuf = Replace$(sBuf, ".", "/")
yourDateVariable = CDate(sBuf)

MsgBox yourDateVariable

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

There are several problems with your proposed aaproach:

  1. Converting strings to date serial can be problematic, with uncertanty if Excel will interpret the date as dd.MM or MM.dd. Since you know the format in advance, extract the month and year directly.
  2. \ is not a valid character for sheet names. I've used _, substitute as you wish
Function GetMonthandYear(FileDate As String) As String
    Dim dot1 As Long, dot2 As Long
    Dim m As String, y As String

    dot1 = InStr(FileDate, ".")
    dot2 = InStr(dot1 + 1, FileDate, ".")
    m = Mid$(FileDate, dot1 + 1, dot2 - dot1 - 1)
    y = Mid$(FileDate, dot2 + 1, InStr(FileDate, " ") - dot2 - 1)

    GetMonthandYear = Format$(DateSerial(y, m, 1), "MMMM_yyyy")
End Function

Call it like this

Sub Test()
    Dim FileDate As String
    FileDate = "15.04.2012 16-31-18"

    ActiveSheet.Name = GetMonthandYear(FileDate)

End Sub

Upvotes: 2

Philip Sheard
Philip Sheard

Reputation: 5825

Have you tried using the CDate and Format functions?

Upvotes: 0

Related Questions