How to have Excel recognize date in format MMM YYYY (e.g. Mar 2015)?

Question

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.

Solution

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

Comments

[CharlieRB]

Welcome to Super User. We need a little more information. Is the report being generated as a CSV, being imported into Excel or what? Depending how the report is generated may determine how we can help you.

[chuckyfang]

Kyle, that works beautifully but why do you suppose our system exports the numbers in such a way, and do you know if there's a quick fix? This is because a lot of people pull such reports from the system and I don't think they'll show the diligence to do this simple 'Replace all.'

[Kyle]

@chuckyfang nobody can answer that question without at least knowing what your system is. If you edit your question to provide more information someone may be able to help you.