PDA

View Full Version : [SOLVED] Replace Cell Date with text



had1015
02-17-2014, 02:45 PM
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
02-17-2014, 02:56 PM
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)

http://www.ultraimg.com/images/kYuj6.png

had1015
02-17-2014, 03:36 PM
Thank you for your reply. Please see attachment.

ashleyuk1984
02-17-2014, 04:13 PM
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
02-17-2014, 06:23 PM
Thank you once again ashleyuk1984 I really appreciate your assistance. I will figure out or work around the upper case bit.