Microsoft Excel - Change date format - Asked By Dan on 28-Aug-12 07:14 AM

I column B:B are the YYYY/MM.  I need a macro to change the format from YYYY/MM to MM/YYYY.  For some reason, I cannot get it to change.  examlpe: 2012/01 to 01/2012.  Now in column B:B it has all the months in the format YYYY/MM.  Any ideas why I cannot get it to change?

wally eye replied to Dan on 28-Aug-12 11:13 AM
Are there actually dates in the column, or is it just text that looks like dates?  If it is text, your macro would have to convert it to dates before changing the numberformat:

Option Explicit

Public Sub UpdateFormat()

    Dim arrDates          As Variant

    Dim lngLastRow        As Long
    Dim lngRow          As Long

    On Error Resume Next
    lngLastRow = Worksheets("MySheet").Columns(2).Find(What:="*", After:=[B1], _
      SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
      LookAt:=xlPart, LookIn:=xlValues).Row
    If Err <> 0 Then
      lngLastRow = 0
    End If
    On Error GoTo 0

    If lngLastRow > 0 Then
      arrDates = Worksheets("MySheet").Cells(2, 1).Resize(lngLastRow - 1, 1).Value
      For lngRow = LBound(arrDates) To UBound(arrDates)
        If Not IsDate(arrDates(lngRow, 1)) Then
          arrDates(lngRow, 1) = CDate(Right(arrDates, 2) & "/1/" & Left(arrDates(lngRow, 1), 4))
        End If
      Next lngRow
      Worksheets("MySheet").Cells(2, 1).Resize(lngLastRow - 1, 1).Value = arrDates
      Worksheets("MySheet").Cells(2, 1).Resize(lngLastRow - 1, 1).NumberFormat = "MM/YYYY"
    End If

End Sub

This first finds out how many rows are in column B, then copies the entire column into arrDates.  It then loops through arrDates, checking if each value is a date.  If not, it converts it to a date.  Finally, it writes the array back out to the worksheet, and changes the numberformat to your "MM/YYYY".
Dan replied to wally eye on 28-Aug-12 12:49 PM
It did not change anything?  Anyway, I now have all the dates in column A ( current format: 2012/01 ).  I want to change them to just the month example: Jan =1 Feb=2 Mar=3 and so on.  Sorry for the confusion.  I am spending alot of time and getting no where.  I will keep trying LOL.

wally eye replied to Dan on 28-Aug-12 02:55 PM
I should have mentioned you need to change the MySheet reference to the actual name of your worksheet....
Dan replied to wally eye on 06-Sep-12 12:16 PM
I did change the sheet name.  Still does not work.
wally eye replied to Dan on 11-Sep-12 03:18 PM
I see you have posted several threads on this topic, and still have not gotten the results you want.  At this point, I think you need to post a workbook, with dummy data if necessary, to get an answer that will work.