Transpose date macro
 

Does anybody know how to transpose text in a field?

I have a column containing dates Jun-01-1900. I need to change it to say
01-Jun-1900

Also, some of the entries may have only one didgit for the day (Jun-1-1900)

There are over 900 entries in this column so I do not want to do it
manually.

Any help will be greatly appresicated

Henry

If the dates shown are shown as text then the following should do

=SUBSTITUTE(MID(A1,FIND("xx",SUBSTITUTE(A1,"-","xx",1))+1,2)&"-"&LEFT(A1,3)&"-"&MID(A1,FIND("xx",SUBSTITUTE(A1,"-","xx",2))+1,4),"--","-")

If these are shown as dates (as in numers) then take a look at the date
function Text function


"Henry Lifton" wrote in message
. ..
Does anybody know how to transpose text in a field?

I have a column containing dates Jun-01-1900. I need to change it to say
01-Jun-1900

Also, some of the entries may have only one didgit for the day
(Jun-1-1900)

There are over 900 entries in this column so I do not want to do it
manually.

Any help will be greatly appresicated

Henry

Thanks so much for your brilliant solution. You saved me hours and hours
of work

Regards
Henry


"N Harkawat" wrote in
:

If the dates shown are shown as text then the following should do

=SUBSTITUTE(MID(A1,FIND("xx",SUBSTITUTE(A1,"-","xx",1))+1,2)&"-"&LEFT(A1
,3)&"-"&MID(A1,FIND("xx",SUBSTITUTE(A1,"-","xx",2))+1,4),"--","-")

If these are shown as dates (as in numers) then take a look at the
date function Text function


"Henry Lifton" wrote in message
. ..
Does anybody know how to transpose text in a field?

I have a column containing dates Jun-01-1900. I need to change it to
say 01-Jun-1900

Also, some of the entries may have only one didgit for the day
(Jun-1-1900)

There are over 900 entries in this column so I do not want to do it
manually.

Any help will be greatly appresicated

Henry