Date formatting

[vbaNewby]

Hi guru's!

I have two cells that look like the following:

Cell A1: Jan 1, 11
Cell A2: Feb 2, 11

In VBA, I need to reformat these so they look like this.

Cell A1: 1/1/2011
Cell A2: 2/2/2011

The purpose behind this is so that I can use datediff in the correct format.

Is there an easy way to change up this date format?

Thanks in advance.

 

[shg]

If they are dates rather than text that looks like dates, DateDiff doesn't care what the formats are.

 

[vbaNewby]

If they are dates rather than text that looks like dates, DateDiff doesn't care what the formats are.

Actually they are formatted as text currently. Is there a way to programmatically change the cells formats?

 

[shg]

You could just select the column and do Data > Text to columns, Finish.

If you want to do it in code,

    Dim cell        As Range
 
    For Each cell In Range("A1:A2")
        If IsDate(cell.Text) Then
            cell.Value = CDate(cell.Text)
            cell.NumberFormat = "m/d/yy"
        End If
    Next cell

 

[vbaNewby]

You could just select the column and do Data > Text to columns, Finish.

If you want to do it in code,

    Dim cell        As Range
 
    For Each cell In Range("A1:A2")
        If IsDate(cell.Text) Then
            cell.Value = CDate(cell.Text)
            cell.NumberFormat = "m/d/yy"
        End If
    Next cell

Thanks for the help shg4421!