Macro to convert dates format from yyyy mm dd to yyyy-mm-dd

[Gators]

Hello,

I have a 20,000 line Excel sheet...
And all the dates are entered like this:
2009 02 08

I want to add a dash, so they look like this
2009-02-08


so I added a command button, and this code, but its not searching the sheet and adding the dash's...Can anyone help?

Please Login or Register  to view this content.

[GuruWannaB]

It looks like you did a macro recording...

Please Login or Register  to view this content.

This defines what cell the rest of the macro is to affect...in this case..ONLY cell A1

Try doing this instead

Please Login or Register  to view this content.

[Gators]

Hmm, tried that, didn't do anything to the sheet

[GuruWannaB]

Ooops...sorry...try this...simplistic overall - works in excell 2003

Please Login or Register  to view this content.

[Gators]

Hey,

Hmm, this almost worked, it just selected the whole sheet this time, but didn't replace the date format for me in B2.

My Range is B2:B19760

[GuruWannaB]

when you click on B2...what does it say in your formula bar?

[Gators]

it just shows the date like this
'1994 02 04

it has that little ' in the formula bar, even though it doesn't exist in the cell.

[GuruWannaB]

Ok...thats the reason why. What is in the cell is not a date, but rather something being considered as text...the ' indicates that what follows is text. Thus the macro can't find the Year, Month, or Date from text...as excel doesn't recognize the data as a date and hasn't assigned those variables.

[Gators]

Hmm,

Im not sure if I follow...If thats the case, wouldn't it just be a simple matter of right clicking > Format Cells > and changing it from text to date?

[GuruWannaB]

Insert a column to the right of column B and copy this code into C1

Please Login or Register  to view this content.

Copy and paste it for the entire column C.

Then copy column C and paste special (Values) back into column B

then delete column C.

This should have converted your text into a date.


Addtional Note...to copy down the entire column...click on C1 and in the bottom right hand corner of the cell outline you will see a small box...double click it

[GuruWannaB]

Hmm,

Im not sure if I follow...If thats the case, wouldn't it just be a simple matter of right clicking > Format Cells > and changing it from text to date?

Not really....the cell would then be formatted for a date...but the value in the cell is still text (dictated by the ')

[Gators]

So how would this be fixed, so I can apply a Macro?

[GuruWannaB]

Think we crossed replies...look above a few posts - you could record this process for a macro if you wanted...if you will be repeating this on a regular basis.

[Gators]

Hey,

Alright, I did what you suggested above, it worked...(As far as inserting a column, etc...) but is there no way to trigger this all by using VBA inside a Macro...Only reason I ask is because it will be for someone who isn't completly computer literate...So if I can just add a command button and it triggers the VBA code and does all this - that would be best.

The command button is already there - it would just be a matter of down filling that formula you just gave me..I use to have a VBA code that you could just insert the formula within vbe and execute the macro and it would down fill, would you know a code to do this?

[Gators]

Basically, I dont want to have the Formula in the Excel sheet...

I just want it within the VBA Sub tags....

and when the command button is clicked, it will trigger the down fill of this formula

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Range("C1:C19759").FillDown
End Sub


Sort of like that

[GuruWannaB]

This is the macro to convert the Text to Date

Please Login or Register  to view this content.

[GuruWannaB]

I've got to run home (about an hour away) but I will log on there and check on your progression.

GWB

[Gators]

Ive tested this Macro, and it works flawlessly with no errors, but
instead of re-formatting the dates, it just fills the first 12 lines of column B in with
#VALUE

then fills #REF for the rest of the 19748 lines.

Any idea's?

[GuruWannaB]

my mistake...I produced this on an existing spreadsheet I was working on and was not working at the top of the spreadsheet, or in the same columns..the references for the formula were wrong if you are starting at B2. Here is the change you would need to make to correct it:

Instead of this in the macro

Please Login or Register  to view this content.

Replace it with

Please Login or Register  to view this content.

It's the attention to details like that, that can ruin a good thing! lol

[Gators]

Hey All,

I found a solution to this:

Please Login or Register  to view this content.