Formatting Date in VBA?

[Matt_O]

Hi Folks,

I haven't had any luck in getting my code to reformat Date from 5/14/2018 to something without the forward slash. The code I found when I searched for a solution simply doesn't work for some reason.

Here's my code showing all the iterations I've tried to reformat the Date.

    Dim aDate As Date
    aDate = Date  ' returns 5/14/2018
    
    ' version 1
    Dim myDate As String
    myDate.Value = format(aDate, "yyyy-mm-dd")

    ' version 2
    Dim myDate As String
    myDate = format(aDate, "yyyy-mm-dd") 

    ' version 3
    Dim myDate As Date
    myDate = format(Date, "yyyy-mm-dd")

    ' version 4
    Dim myDate As String
    myDate = format(Date, "yyyy-mm-dd")

The VBA editor always changes the F in Format to a lowercase and I get the error "Wrong number of arguments or invalid property assignment."

All around the web I kept finding this code

(variable) = Format(Date, "DD-MM-YYYY") ' or similar

I'm stumped again in VBA. Although I did figure out the difference between Now and Date!

Thanks in advance to anyone who replies.

Cheers,
Matt

 

[Norie]

Matt

Have you named anything 'format'?

 

[alansidman]

When I tested the following, it worked for me.

Option Explicit


Sub fooDate()
    Dim aDate As Date, myDate As String
    aDate = Date  ' returns 5/14/2018
    
    ' version 1
    myDate = Format(aDate, "yyyy-mm-dd")
    MsgBox (myDate)


    ' version 2
    myDate = Format(aDate, "yyyy-mm-dd")
    MsgBox (myDate)
    
    ' version 3
    myDate = Format(Date, "yyyy-mm-dd")
    MsgBox (myDate)
    ' version 4
    
    myDate = Format(Date, "yyyy-mm-dd")
    MsgBox (myDate)
End Sub

 

[Matt_O]

Thanks for the prompts replies Norie and Alan.

When I copied Alan's code the editor made all the Fs lowercase gain. It turns out I did have a sub called "format" that I didn't use and completely forgot about it!

Alan's code works but the editor refuses to maintain the uppercase F in format. Maybe a re-boot will fix this. At least the code works.

Thanks again, much appreciated!

Matt