Date String manipulation in Excel VBA

Question

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

Number format doesn't change the underlying value. myDate = Format$(Date, "mm/dd/yyyy") — BigBen
– BigBen, Commented Aug 25, 2021 at 20:19
you are right. Can you please explain what does the "$" sign do? — JackeyOL
– JackeyOL, Commented Aug 28, 2021 at 18:10
Format$ returns a String. Format returns a Variant/String. Either works here — BigBen
– BigBen, Commented Aug 28, 2021 at 18:21

Gustav · Accepted Answer · 2021-08-25 20:26:56Z

1

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")

answered Aug 25, 2021 at 20:26

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Date String manipulation in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related