Date Format in VBA

[Romulo Avila]

Good Morning,


I have a problem, I have in the worksheet in cell "A1" a date "14/02/2017", I need inside VBA that this value be loaded in the variable in the format "yyyy-mm-dd", but in the way that I know I can not, it only appears dd / mm / yyyy, how should I proceed?


Sub test()
Dim InvoiceDate As Date
InvoiceDate = Format(Range("A1").Value, "YYYY-MM-DD")
Range("B10") = InvoiceDate
End Sub

Thanks for the support

[YasserKhalil]

The variable would be String not date
And the target cell would be formatted as text
Try this code

Sub Test() Dim InvoiceDate As String

InvoiceDate = Format(Range("A1").Value, "YYYY-MM-DD")
Range("B10").NumberFormat = "@"
Range("B10") = InvoiceDate
End Sub

[Paul_Hossler]

Separate the 'Data' from the 'Display'





Sub test()
Dim InvoiceDate As Date

InvoiceDate = Range("A1").Value

Range("B10").Value= InvoiceDate

Range("B10").NumberFormat = "YYYY-MM-DD"

End Sub

Internally B10 will be a Date, but it will display in yyyy-mm-dd format


If you want to use it you can

a) make it tomorrow by InvoiceDate = Range ("B10").Value + 1

b) include it in a message string like, "Please pay by " & Format(InvoiceDate, "yyyy-mm-dd")