Convert string variable to date

Question

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?

chris neilsen · Accepted Answer · 2012-04-15 19:45:05Z

There are several problems with your proposed aaproach:

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.
\ 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

Philip Sheard · Accepted Answer · 2012-04-15 13:30:57Z

0

Have you tried using the CDate and Format functions?

answered Apr 15, 2012 at 13:30

Philip Sheard

5,8335 gold badges30 silver badges42 bronze badges

1 Comment

Sam Stephenson Over a year ago

Never heard of CDate but have just found ToDateTime method currently experimenting

Sam Stephenson · Accepted Answer · 2012-04-16 01:45:24Z

0

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

edited Apr 16, 2012 at 1:45

Sam Stephenson

5,1905 gold badges29 silver badges44 bronze badges

answered Apr 15, 2012 at 13:38

Alexander Burov

1,32211 silver badges17 bronze badges

1 Comment

Sam Stephenson Over a year ago

It seems your method should work but I keep geting a type mismatch

Sub GetMonthandYear(MonthYear, FileDate)     Dim dateBuf As Date     dateBuf = CDate(Replace$(FileDate, "-", ":"))      End Sub

Collectives™ on Stack Overflow

Convert string variable to date

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related