Trouble Formatting Dates

**jpgoeth** · 05-25-2007, 02:00 PM

I'm extracing some data from a database, and the dates I get are in the format yyyy/mm/dd. When I go to "format cells" and change the format to dd/mm/yyyy (or any other format), the values don't change unless I go and double-click the cell to edit it.

Why is this happening? Is there a way to fix it?

JP

**Paul** · 05-25-2007, 11:06 PM

If all your dates are in one column, you can run this macro in a module to update them all at once:

Sub updateDates()
    Dim i, iLastRow As Long    
    iLastRow = Range("A65536").End(xlUp).Row
    For i = 1 To iLastRow
        Cells(i, 1).Value = Cells(i, 1).Value
    Next i    
End Sub

This code assumes your dates are in column A, from row 1 through x. If your data is elsewhere you'll need to change the following:
Red text above are column references. Change those to match your column. (The 1's represent the first column, or A. So column E would be 5, etc.)
Blue text above represents the starting data row. If your data starts in another row, change it to that.

**Richard Schollar** · 05-26-2007, 04:01 AM

Hi

Easiest way would be to select the column that contains these 'dates' and go Data>TextToColumns>Next>Next>and choose a Column Data Format of Date (YMD from the dropdown) and click Finish.

Hope this helps!

Richard