Problems converting date to text

[mtterry]

I have a date stored in a variable: invDate (mm/dd/yyyy). As part of my sub I need to convert it to text in the format "yyyy-mm-dd". I've seen several solutions on this board, but for some reason they aren't working for me:

expItemDate = VBA.Format(CStr(invDate), "yyyy-mm-dd") - doesn't work, it returns a date in the original mm/dd/yyyy formatexpItemDate = VBA.Format(invDate, "yyyy-mm-dd") - doesn't work either, same thing it returns mm/dd/yyyy format

 

[Rick Rothstein]

I have a date stored in a variable: invDate (mm/dd/yyyy). As part of my sub I need to convert it to text in the format "yyyy-mm-dd". I've seen several solutions on this board, but for some reason they aren't working for me:

expItemDate = VBA.Format(CStr(invDate), "yyyy-mm-dd") - doesn't work, it returns a date in the original mm/dd/yyyy formatexpItemDate = VBA.Format(invDate, "yyyy-mm-dd") - doesn't work either, same thing it returns mm/dd/yyyy format

Why are you converting the date to a text string inside the Format function (in order to apply the "yyyy-mm-dd" date pattern, the first argument to the Format function needs to be a real date)? Try it this way...

expItemDate = Format(invDate, "yyyy-mm-dd")

 

[mtterry]

Why are you converting the date to a text string inside the Format function (in order to apply the "yyyy-mm-dd" date pattern, the first argument to the Format function needs to be a real date)? Try it this way...

expItemDate = Format(invDate, "yyyy-mm-dd")

What you posted was actually the first thing I tried. I used the CStr because because it didn't work. I just tried again and your suggestion is yielding the same result, a date in mm/dd/yyyy format.

 

[Rick Rothstein]

What you posted was actually the first thing I tried. I used the CStr because because it didn't work. I just tried again and your suggestion is yielding the same result, a date in mm/dd/yyyy format.

Then your "date" is not a real recognizable date. How is the invDate variable being assigned its value and what is the value just before the Format statement receives it?

 

[mtterry]

Ok - below is the code (a portion) that assigns the value to invDate - it's from a textbox in a userform

If invDateTextBox <> "" Then
If IsDate(invDateTextBox.Value) Then
invDate = VBA.Format(CDate(invDateTextBox.Value), "mm/dd/yyyy")
Else

 

[mtterry]

Bump

 

[mtterry]

Anyone?

 

[xenou]

Provide a sample input date that isn't working as expected. Also you should provide the Else code (what happens if the input is not a date?)

 

[mtterry]

Okay, the current date I'm using is 8/1/2019, below is the full code I have in the user form to set invDate:

If invDateTextBox <> "" Then
If IsDate(invDateTextBox.Value) Then
invDate = VBA.Format(CDate(invDateTextBox.Value), "mm/dd/yyyy")
Else
MsgBox "Please enter a valid invoice date to proceed."
Exit Sub
End If
Else
MsgBox "Please enter an invoice date to proceed."
Exit Sub
End If

I do want invDate to be a date in the format 8/1/2019, so that's ok. What I'm struggling to do is set the value of another variable to the string value "2019-08-01".

 

[xenou]

your code is working fine for me.

Steps I took to recreate the problem:

• create a new userform with a textbox and a button
• in the button added code below
• entered 8/1/2019 in the textbox and hit the button

Private Sub CommandButton1_Click()
    MsgBox VBA.Format(CDate(Me.TextBox1.Value), "yyyy-mm-dd")
End Sub

Edit:
Here's a little more code to get a date and a formatted date in variables:

Private Sub CommandButton1_Click()
 
Dim invDate As Date
Dim invDateString As String
    
    If IsDate(Me.TextBox1.Value) Then
        invDate = CDate(Me.TextBox1.Value)
        invDateString = Format(invDate, "yyyy-mm-dd")
        MsgBox invDate
        MsgBox invDateString
    Else
        MsgBox ("Please enter a valid date in TextBox1")
    End If
    
End Sub