I have a system that has outputs reports to Excel but Excel does not recognize the format as a date--"Mar 2015" to be "Mar-15". This makes pivoting the data hard because the date column is sorted alphabetically.
Other than using a formula (=datevalue), what can I do to make Excel recognize the "month year" as a date? I don't want to add another column to data, and it's strange that when I go into the text of date cells and press 'Enter', the entry is then recognized as a date.
11 Answer
Select your data and press Ctrl+H to bring up the Replace dialog box. Enter a single space into both the Find What: and Replace With: text boxes and click replace all. This essentially has the same effect as you clicking in the formula bar for each cell and pressing enter.
Alternatively, select the data and use this macro:
Public Sub MakeDate() With Selection .Value = .Value .NumberFormat = "mmm-yy" End With
End Sub
