locked
Reformat date from DDMMYY to DDMMYYYY adding back leading zeros RRS feed

  • Question

  • Hello, please help! I'm trying to reformat a csv file with a macro. I've written most of the macro but one bit has got me stumped. I have one column B with the date in. I need to reformat the date from the current format (ddmmyy) to (ddmmyyyy). Easy? Yes, but i cant figure out the nexr bit... The complication is that I also need to add the leading zeros back which are stripped out when opened in excel. So for example a date of 040313 becomes 40313 when opened in excel. I need to be able to do a select column, and re-format so that the date is displayed DDMMYYYY, and if the day element is 01-09 this is added back as 01MMYYYY. I have tried various ways, but they kept messing up the date... Thank you!
    Thursday, May 30, 2013 3:54 PM

Answers

  • This assumes that all dates are after 1/1/2000 - you could change to a conditional inclusion of "19" or "20" based on the year range, but this is simpler (for now, at least) I have assumed that the dates start in row 2 and that there are no blank cells or cells without a five or six digit value.

    Sub RedoDateStrings()
        Dim rngDate As Range
        Dim rngDates As Range

        Set rngDates = Range(Range("B2"), Cells(Rows.Count, "B").End(xlUp))
        rngDates.NumberFormat = "@"
        For Each rngDate In rngDates
            If Len(rngDate.Value) = 5 Then
                rngDate.Value = "'0" & Left(rngDate.Value, 3) & "20" & Right(rngDate.Value, 2)
            Else
                rngDate.Value = Left(rngDate.Value, 4) & "20" & Right(rngDate.Value, 2)
            End If
        Next rngDate

    End Sub

    Thursday, May 30, 2013 4:58 PM

All replies

  • This assumes that all dates are after 1/1/2000 - you could change to a conditional inclusion of "19" or "20" based on the year range, but this is simpler (for now, at least) I have assumed that the dates start in row 2 and that there are no blank cells or cells without a five or six digit value.

    Sub RedoDateStrings()
        Dim rngDate As Range
        Dim rngDates As Range

        Set rngDates = Range(Range("B2"), Cells(Rows.Count, "B").End(xlUp))
        rngDates.NumberFormat = "@"
        For Each rngDate In rngDates
            If Len(rngDate.Value) = 5 Then
                rngDate.Value = "'0" & Left(rngDate.Value, 3) & "20" & Right(rngDate.Value, 2)
            Else
                rngDate.Value = Left(rngDate.Value, 4) & "20" & Right(rngDate.Value, 2)
            End If
        Next rngDate

    End Sub

    Thursday, May 30, 2013 4:58 PM
  • Perfect, thank you.
    Friday, May 31, 2013 8:45 AM