reformat date issue

[englishrob]

I’m on Excel 2010<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
This is a piece of code designed to reformat date from US to <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region w:st="on"><st1lace w:st="on">UK</st1lace></st1:country-region> (i.e. mm/dd/ccyy to dd/mm/ccyy) based on cells selected by the user. <o></o>
<o> </o>
Although the string variable shows the reformat correctly, when I place the value in the cell it reverts to type.<o></o>
<o> </o>
Anybody got any ideas?<o></o>
<o> </o>
Set rSel = Selection<o></o>
<o> </o>
For Each rCel In rSel<o></o>
<o></o>
sMyText = rCel.Text<o></o>
<o></o>
wk2.Range("G" & rCel.Row).Value = Mid(sMyText, 4, 3) & Mid(sMyText, 1, 3) & Mid(sMyText, 7, 4)<o></o>
<o></o>
<o></o>
Next rCel


thanks in advance<o></o>

 

[Norie]

It's a string you are putting back, try putting back a 'real' date value.

wk2.Range("G" & rCel.Row).Value = DateValue(Mid(sMyText, 4, 3) & Mid(sMyText, 1, 3) & Mid(sMyText, 7, 4))

By the way, did you try just formatting the dates?

 

[englishrob]

thanks Norie - that worked. where is Yaren?