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

cell formatdate timemicrosoft excelmicrosoft-excel-2010

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.

Best 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

Related Solutions

Excel – How to parse a nonstandard date format in Excel

You'll need to do string parsing, then pass an appropriate formatted value into DATEVALUE – something like this:

=DATEVALUE(RIGHT(A1,2)&"-"&MID(A1,6,3)&"-"&LEFT(A1,4))

(edit: updated formula - tested and it works for me on one example - may need to refine depending on your input)

Excel – How to set date format when manually inserting date on excel, without changing regional settings

The Worksheet_Change event could essentially accomplish what you're asking for, but you need to be sure the user intends things to change. I've added a 'Yes/No' choice box to my solution to prevent any accidental changes. Doing this without a choice box is risky because if you tried to edit an existing value the it would change back to the original formatting.

This code should 'live' inside the VBA code for the sheet you're wanting it to act on:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iDay, iMonth, iYear As Integer
Dim solutionRange As Range

'the cells you want the date to change for
Set solutionRange = Range("D4:D12")

'only triggers if the user's changing one cell
If Target.Count = 1 Then
    If (Not (Intersect(solutionRange, Target) Is Nothing) And (Target.Value > 0)) Then
        'disable application events so this change doesn't trigger itself
        Application.EnableEvents = False

        iDay = Left(Target.Value, 2)
        iMonth = Mid(Target.Value, 4, 2)
        iYear = Right(Target.Value, 4)

        ' Give the user a choice to say whether they need the date changed. You *can* remove both the 'If' and 'End If' if you really need to, but I'd strongly suggest keeping them.
        If (MsgBox("Have you entered your date in the format 'MM/DD/YYYY'?", vbYesNo) = vbYes) Then
            Target.Value = DateValue(iMonth & "/" & iDay & "/" & iYear)
        End If

        Application.EnableEvents = True
    End If
End If
End Sub

Best Answer

Related Solutions

Excel – How to parse a nonstandard date format in Excel

Excel – How to set date format when manually inserting date on excel, without changing regional settings

Related Question