excelvbastringdateunique

Change Date To String with format "mmm-yy"


I have a column of dates. I want to modify the format and convert to text.

I'm doing this because I have dates with various days, but need to only find the unique month-year combos. When I format the dates to show "mmm-yy" and then try to get the unique values it still sees the day values and provides more unique values than I need.

To resolve this, I'm trying to remove the "value" of the dates and convert to a string which I can then remove the days.

Dates I'm trying to convert are showing in the sheet as 31-DEC-22 (for example) and all show a warning "This cell contains a date string represented with only two digits for the year." I don't care about the year digits, but unsure if this warning is part of the problem.

I tried to convert to a string:

dateValue = CStr(dateValue)

Column.NumberFormat = "General"

str = Application.WorksheetFunction.Text(dateValue, "MMM-YY")

Dim str As String = Format(dateValue, "MMM-YY")

With all of the above when I add the resulting values to a cell, it shows as properly formatted, but when viewing the value still shows the full date with day and show as Custom Format.

FYI the only way I get this to work, is by adding a single quote to each value.


Solution

  • Excel will convert any string containing a valid date into a date unless, explicitly tell excel that is not a date like by adding a single quote

    mySheet.Cells(1,1).Value = "'" & str
    
    

    or by changing the format to text before assigning the value

    mySheet.Cells(1,1).NumberFormat = "@"
    mySheet.Cells(1,1).Value = str