Multiple dates format in same column

[ryaner84]

Hi guys,

i am facing a situation where i have multiple format of dates in a column. An example will be row 1-5 will be 15/1/11 and row 6-10 will be 1/15/11. however i am very sure that all these dates are supposed to be January.

is it possible for me to store a variable such as the month january stored as integer "1"

and compare the field data example
if the date is 1/15/2011 then the month is january if the date is 12/1/2011 it will rename to 1/12/2011?

Regard
Ryan Yu<!-- / message -->

 

[ryaner84]

hi i got the solution wish to share with othes

curmonth = 7
Range("A1").Select
lastrow = ActiveSheet.UsedRange.Rows.Count
    For i = 2 To lastrow
    ActiveSheet.Cells(i, 5).Select
    ActiveSheet.Cells.NumberFormat = "mm/dd/yyyy"
    
    'excelmonth is the date of the cell
    excelmonth = ActiveSheet.Cells(i, 5)
    'managed to capture the month
    extractmonth = Month(excelmonth)
    If extractmonth = curmonth Then
            ActiveSheet.Cells.NumberFormat = "mm/dd/yyyy"
            Else
    
            'to extract the date and year of excelmonth
            extractdate = Day(excelmonth)
            extractyear = Year(excelmonth)
    
            'create a new date value
            Dim newdate As Date
            newdate = DateSerial(extractyear, extractdate, extractmonth)
            'write the new date back to cell
            ActiveSheet.Cells(i, 5).Value = newdate
       
            ActiveSheet.Cells.NumberFormat = "mm/dd/yyyy"
            End If
    
    Next i
End Sub