Search code examples
excelvbaworksheet-function

How do I use WorksheetFunction.Text to convert a date from M/D/YY to YYYY-MM-DD?

I have a date in cell B7 in my worksheet that is formated as 9/1/2019, I want to convert it to a text string in the format YYYY-MM-DD or 2019-09-01. I tried using WorksheetFunction.Text and searched the web for examples, but my code just generated a "True" result instead of a reformatted date.

This code did not work:

Dim FormattingValue As String
Dim FormattingResult As String

FormattingValue = WS1.Range("B7").Select

FormattingResult = WorksheetFunction.Text(FormattingValue, "YYYY-MM-DD")

Debug.Print FormattingResult

The Debug.Print returns the value "True" and not a date in the YYYY-MM-DD format.

The code below does work thanks to the contributors:  

Dim FormattingResult As String 

FormattingResult = Format(WS1.Range("B7").Value, "YYYY-MM-DD")  

Debug.Print FormattingResult  

This successfully converted 11/5/19 to 2019-11-05
Solution

  • Select doesn't return a value, so this doesn't work as you expect:

    FormattingValue = WS1.Range("B7").Select

    In VBA you use can Format(), and there's no need to select anything:

    Debug.Print Format(WS1.Range("B7").Value, "YYYY-MM-DD")