"Gerald" wrote in message
...

"Fred Smith" wrote:

Now you know you have text, not an Excel date. You have two choices:

1. Keep the data as text, in which case you would convert to dd/mm/yyyy
format
using:
=mid(a1,4,3)&left(a1,3)&right(a1,4)
....

thanks for your reply

used the formulae =MID(A5,4,3)&LEFT(A5,4)&RIGHT(A5,1) to get the output as
dd/mm/yyyy

for the fields with single digit dates e.g 06/07/06 works fine and gives
me
result as 07/06/06 but if the field has 06/10/06 it gives me an output as
6/06/106

Wouldn't =MID(A5,4,3)&LEFT(A5,3)&RIGHT(A5,2) make more sense?
I can't see why you've taken 4 characters for the month and only one for the
year? That wasn't what Fred suggested.
--
David Biddulph