Could Use a Little Help formating Date Using VBA

[NewYorkFashionToGo]

Hi Thanks for looking at this post. I have a column in a worksheet that is imported in as 2006-06-24T21:24:22.000Z The first 10 characters is the date. I used one step a Left function to get it to appear as this 2006-06-24 in a new column. I want it to appear as this 06/24/06, I went into format cells and changed it to that format using Date and then tried it using custom. It didnt budge. The only time it does budge is when I click on each cell and hit enter, Then it changes. How do I get around that? Is there a trick to do the whole column in Mass to get it to change over? Its not Budging until I do that, Does anyone know?

 

[AnotherHiggins]

You don't need a macro to do this. Do you require an answer in VBA? If not, then the question really should have been posted in forum68, the Microsoft: Office Forum.

I assume you used something like [COLOR=blue white]=LEFT(A1,10)[/color] to get the date, right? Well that returns a text string. You can't format a text string as a date. Change your formula to
[tab][COLOR=blue white]=value(LEFT(A1,10))[/color]
or
[tab][COLOR=blue white]=LEFT(A1,10)*1[/color]
to return a number. Then format the answer column however you want.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[NewYorkFashionToGo]

wow good trick, Thanks very much, I just learned something. That was driving me crazy. Thanks so much for your quick reply

 

[AnotherHiggins]

Glad to help!


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

and check out Why do Dates and Times seem to be so much trouble? faq68-5827.

Skip,
[sub]
[red][/red]
[/sub]