Coverting dates

[Tony]

Hi Group,

I have inherited a large spreadsheet full of payments
(about 5000 of them). Some dates are entered like so
01/01/04, 01/1/2004, 01.01.04, 01.1.2004.

Excel does not recognise the dates with full stops in
them. Is there a fast and efficient way for me to convert
and standardise all dates to dd/mm/yyyy?

Regards

Tony

 

[Tod]

You can use the substitute function in a formula or in
code.

In formula:

Assuming your dates are in column A, put this formula is
an empty column and fill down. Then copy the values over
those in column A. Then format the column for you
prefered date format.

=IF(LEN(SUBSTITUTE(A1,".",""))<LEN(A1),DATEVALUE
(SUBSTITUTE(A1,".","/")),A1)

This says that if the date has periods, replace them with
slashes.

In code, you do almost the same thing except that you
don't have to do the copy and pasting and formatting.

Dim Cell As Range
For Each Cell In ActiveSheet.Range("A2:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)
If Len(Application.Substitute
(Cell.Value, ".", "")) < Len(Cell.Value) Then
Cell.Value = Application.Substitute
(Cell.Value, ".", "/")
Cell.NumberFormat = "dd/mm/yyyy"
End If
Next Cell

tod

 

[Niek Otten]

Hi Tony,

=REPLACE(REPLACE(A1,3,1,"-"),6,1,"-")*1

Then format as date.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Niek Otten]

In addition to my previous reply: The "-" symbol may be "/" in your case.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Tony]

Tod,
Thank you for the response. Apologies for not replying
sooner but have just returned from a weekend break.
I liked the VB solution.
It successfully converted the periods to dd/mm/yy but now
I cannot format them to dd/mm/yyyy. I have checked the
affected cells (about 470) under general formatting and
they do not display the Excel date function i.e. 35321.
Is it possible to convert these cells to dates?

Regards

TOny