Replace Cell Date with text

[had1015]

Hi,

I need some help as I'm using macros for formatting csv files to excel reports. There are instances where the csv data may have something like "APR14", "MAY15", "JUN16" or any other month with the year following. When I get then into excel the year always is turned on the left side thereby becoming dates something like "14-Apr" or "14-May" of the current year. I would like vba code that will convert it back to its original format like "APR14", "MAY14", "JUN14".

Any assistance would be greatly appreciated.

[ashleyuk1984]

A sample workbook would be handy. Only needs to contain a few rows, just so that we can see what we are working with.
Should be simple enough to do. But until we see it, and see how it's actually formatted, I can't write anything.

I'm guessing something along these lines. (but in VBA obviously)

[had1015]

Thank you for your reply. Please see attachment.

[ashleyuk1984]

I hope this helps.

This searches through every used cell, and checks if the value is a date or not.
If it's a date, then it formats the cell to "mmmyy". For example 18/02/2014 when formatted to "mmmyy", will be "Feb14".
I couldn't work out how to change it to FEB14 (uppercase) maybe someone else can help with that bit.

Sub ChangeDateFormat()

   For Each cell In ActiveSheet.UsedRange.Cells
        If IsDate(cell) Then
        cell.NumberFormat = "MMMYY"
        End If
   Next

End Sub

[had1015]

Thank you once again ashleyuk1984 I really appreciate your assistance. I will figure out or work around the upper case bit.