Dates exported as text

[michael.a7]

I am running Sage in the UK and when I export a report the dates are
exported as text.

I then need to change the format to "dd/mm/yy" - select the cell and
then press enter.

I have written some VBA code to automate this - but it does not work on
dates such as 25th March 2005 - because Excel thinks that 25 is the
month and 3 is the day.

Has anyone experienced a similar problem - Is there a solution?

Many Thanks,
Michael

 

[paul.robinson]

Hi
Excel doesn't like the th or rd in 24th or 23rd.
In VBA you could try

Public Sub test()
With Application.WorksheetFunction
Range("C1").Value =
Format(DateValue(.Substitute(.Substitute(Range("A1"), "th", ""), "rd",
"")), "dd/mm/yy")
End With
End Sub

mind the line wrap. Your original date is in A1.

In Excel try
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1, "th", ""), "rd", ""))

in cell C1, say, where C1 has the general cell format. Now change the
cell format to date and choose the format you require

regards
Paul

 

[michael.a7]

Thanks Paul :