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
```

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel