Date format issue

**maacmaac** · 04-22-2010, 04:35 PM

I am importing a large excel file from with ~2500 line items. I am having issues with excel formating some of the dates to a year 19xx format instead of a 20xx format.

For example: some of the values that are being copied are 3/15/43 or 9/22/47. Then value is being converted to 03/15/1943 and to 09/22/1947. Is there an easy way to convert these value back to a 20xx format. I tried using the custom button with "mm/dd/20yy" but didn't work. Thanks for any comments.

**shg** · 04-22-2010, 05:43 PM

See http://support.microsoft.com/kb/214391

**maacmaac** · 04-22-2010, 06:46 PM

Link helped a little but couldn't quite figure how to incorporate. The problem is that the data I am getting from another spreadsheet is already messed up (i.e. there are dates that are formatted as 19xx and they shouldn't be)

I did come up with a work around that I think might work. I am going through each cell to determine if it is less than 01/01/2000. If it is less than 01/01/2000, then add 100 years to the year.

The code I tried using is as follows but can't figure out why it won't work. Not sure if the "Date" function is available in VBA

    With wsAnalysis
        EndRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 2 To EndRow
            If .Cells(i, 5).Value < 36526 Then
                .Cells(i, 5).Value = WorksheetFunction.Date(Year(.Cells(i, 5)) + 100, Month(.Cells(i, 5)), Day(.Cells(i, 5)))
            End If
        Next i
    End With

Thanks for any comments

**shg** · 04-22-2010, 07:46 PM

Think you wanna subtract 100 years:

    With wsAnalysis
        For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            With .Cells(iRow, 5)
                If .Value < #1/1/2010# Then
                    .Value = DateSerial(Year(.Value) - 100, Month(.Value), Day(.Value))
                End If
            End With
        Next iRow
    End With

**maacmaac** · 04-22-2010, 08:31 PM

Shg, thanks for the great tip. I actually need to add 100 years. If the current value of a cell is 09/18/1947, I need it to convert to 09/18/2047. I changed code to add 100 and ran it through a number of examples and it appears to be correctly (also had to change the "#1/1/2010#" portion to "#1/1/2000#"). Thanks again for your assistance

    With wsAnalysis
        For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            With .Cells(iRow, 5)
                If .Value < #1/1/2000# Then
                    .Value = DateSerial(Year(.Value) + 100, Month(.Value), Day(.Value))
                End If
            End With
        Next iRow
    End With