18

I have one more time a problem:

I want to convert from Strings to dates in VBA

The Strings look like: YYYY-DD-MM

The date should be like: DD.MM.YYYY

I know, normally you do this with the method cdate(), but it doesn't work here. I think it's because the structure of the string is bad to convert.

thanks for your help

InformatikBabo

InformatikBabo
  • 510
  • 2
  • 8
  • 19
  • you want to transform it into another string of a different formatting or do you want a Date object? –  Nov 06 '13 at 14:45
  • Just wanted to comment that `CDate()` does not appear to work on strings like `"20200109"`. It works on `"2020-01-09"` (with the hyphens), but not on the hyphenless one. I had to break the string up into three parts and then rebuild it with the hyphens to be able to use `CDate()`. I realized just now that I could have converted the string with `Format()` but the larger point is that `CDate()` appears to only work with strings in certain formats. – MBB70 Jan 09 '20 at 21:24

2 Answers2

29
Sub Main()

    Dim strDate As String
    strDate = "2013-06-11"

    Debug.Print "Original Date: ", strDate
    Debug.Print "CDate() Conversion: ", CDate(strDate)
    Debug.Print "Format() as String: ", Format(strDate, "DD.MM.YYYY")

End Sub

and the Immediate Window shows

enter image description here

0

' Try this on for size.

' This is return a strings "date" as the previous day with a different format !!

' Dstring passed equals this format "08/01/2023" .

Function SdateD(byref Dstring) as String
Dim NewDateD 'undefined

    NewDateD = CDate(Dstring) ' This will convert it to #08/01/2023#
    NewDateD = NewDateD - 1 ' now it is #07/31/2023#
    Dstring = Format(Cstr(NewDateD), "m.d.yy") ' it will return the value 7.31.23

End Function

I was pulling a date from a MSWord doc. 08/01/2023

Which was the date after the document was created.

I was wanting to change the name of the file to include the actual date. You can't use "/" in document names as that is the delimiter for directories.

So this works:

  RenameFile OriginalFN & ".Docx", OriginalFN & SdateD(Dstring) & ".Docx"
Nick W
  • 1