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


--
michael.a7
------------------------------------------------------------------------
michael.a7's Profile: http://www.excelforum.com/member.php...o&userid=33027
View this thread: http://www.excelforum.com/showthread...hreadid=528494

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

Thanks Paul :

--
michael.a
-----------------------------------------------------------------------
michael.a7's Profile: http://www.excelforum.com/member.php...fo&userid=3302
View this thread: http://www.excelforum.com/showthread.php?threadid=52849