Excel VBA Using Text

[DonAckerman]

I looked, but don't see this posted, yet. This is a newbie question for someone learning VBA.

I'm looking to format a date as text using a VBA formula. Why? Well, the reason is because the spreadsheet is exported and downstream is wanting a yyyy-mm-dd format. I'm looking to create a version of the date to store it as text.

I've seen the Text format is "=TEXT(Value, Format)", but I don't see how to incorporate it
Is there a way to have a formula like "Date1Text = Text(Today(), "yyyy-mm-dd")"
Or if I assign a date to Date1, something like "Date1Text = Text(Date1, "yyyy-mm-dd")"

(That above doesn't work, but I get the idea it's close.) Thanks in advance! MrExcel is a great resource.

 

[Rick Rothstein]

I'm looking to format a date as text using a VBA formula. Why? Well, the reason is because the spreadsheet is exported and downstream is wanting a yyyy-mm-dd format. I'm looking to create a version of the date to store it as text.

I've seen the Text format is "=TEXT(Value, Format)", but I don't see how to incorporate it
Is there a way to have a formula like "Date1Text = Text(Today(), "yyyy-mm-dd")"
Or if I assign a date to Date1, something like "Date1Text = Text(Date1, "yyyy-mm-dd")"

You can get at most of Excel's worksheet functions using the WorksheetFunction object. For the question you asked, you have to use VBA's Date function to get today's date. The line of code you want is this...

Date1Text = WorksheetFunction.Text(Date, "yyyy-mm-dd")

 

[DonAckerman]

Thanks Rick. I'm learning and this is very helpful! Thank you!