CODEWITHSUNDEEP
excelvbadatetime
JackeyOL
JackeyOL

Reputation: 321

Date String manipulation in Excel VBA

The VBA built-in Date variable can give me today's date (8/25/21 as the time this post is written). However, I really want the date in the mm/dd/yyyy format for future projects and store it in a variable - myDate. I just couldn't think of an easy string manipulation solution to get the desired result.

I've tried put the Date variable in excel sheets and then change the number format using the below code:

[A1]=Date
[A1].NumberFormat="mm/dd/yyyy"
myDate=[A1].value
debug.print myDate

Even though the number format code will change the appearance of cell [A1], making it look like it's in the desired format mm/dd/yyyy, the debug.print line still gives 8/25/21 instead of 08/25/2021

Upvotes: 1

Views: 104

Answers (1)

Gustav
Gustav

Reputation: 55806

A date value holds no format. Apply the format for display - that includes when calling Debug.Print:

Dim myValue As Date

[A1] = Date
[A1].NumberFormat = "mm/dd/yyyy"
myDate = [A1].Value
Debug.Print Format(myDate, "mm/dd/yyyy")

Upvotes: 1

Related Questions