VBA to Fix date format - alter string programmatically

[EXLwiz]

Hi, I have a date field being exported from an asp OMS, This field is sometimes populated as YYYY-MM-DD. When the year is shown first the format I end up with on export in excel looks like this 20/07/8/6/ instead of 8/6/2007

I was looking for some vba code to re-format this definitive pattern so it would be formatted MM-DD-YYYY, to be run on a button click function in my existing macro project.

It needs to be recognized as a TRUE date format because I pull the YEAR out from a helper column. (and yes it'd be GREAT to fix the date format in the OMS befre export, but that will not happen any time soon and i have no control over that change, only this export part!)

Any suggestions how this can be done? THANKS!

using excel 2007

[TMS]

Maybe:

Please Login or Register  to view this content.

Regards, TMS

[Leith Ross]

Hello EXLwiz,

You say "This field is sometimes populated as YYYY-MM-DD". Does that mean there are different date formats besides this one being impported?

[EXLwiz]

Thanks for the quick reply..

Yes infact this works to fix the date format. I had to modify the code to match my column which is in col AA (AA2:AA1000 - AA1 is a header row). but then i tried a few scenarios and it broke down. runtime error 9 subscript out of range. i debugged and found the cell value passed into the array was already formatted correctly as mm/dd/yyyy

It also needs to check for:

whether the cell is empty (some cells will not be populated at all),
if the format is already as desired,
if there's a single digit format used like m/d instead of mm/dd

sample data i tested with (10 rows)

(header cell)
20/07/8/26/
07/07/2007

20/12/4/5

20/07/8/6/
19/87/12/4
08/06/2007
20/12/4/18

[EXLwiz]

Hi @Leith Ross.. yes, i just posted another comment explaining more details.

[TMS]

1. The code catered for the scenario you described
2. You didn't say which column your data was in
3. You didn't say your data had header(s)
4. You didn't say your data had blank cells in it
5. The code caters for single digit month and day using Split
6. The code won't cope if any of the elements are missing
7. The output is formatted as mm/dd/yyyy and it is a true date so that's immaterial.

Don't expect us all to have second sight. You need to describe the data and the issues you have to fix.

Sorry you had to put some effort into fixing your data.

[EXLwiz]

1. The code catered for the scenario you described
2. You didn't say which column your data was in
3. You didn't say your data had header(s)
4. You didn't say your data had blank cells in it
5. The code caters for single digit month and day using Split
6. The code won't cope if any of the elements are missing
7. The output is formatted as mm/dd/yyyy and it is a true date so that's immaterial.

Don't expect us all to have second sight. You need to describe the data and the issues you have to fix.

@TMS - i never meant to offend you in the least and was cetainly not trying to sound critical, i was simply factual and pressed for time when i posted earlier. i'm not that familiar with vba, i do know .NET and am very handy with in-cell formulas, but .. apples and oranges are both fruit right??

so i threw a quick post out hoping i'd get just the piece i needed that changes the format and could manage the rest, but honestly I dont have the time or knowledge enough in vba with arrays (demons, as i call them) to do it myself. I didn't say i changed your code to meet my column name to be critical, just as a matter fact.. it was no biggie (i thought) which col it actually is in, since i do know enough vba to know how to change the columns and adjust for the headercell

to be fair, i did say SOMETIMES the date is goofy format, i thought that would have been an indicator to check if it needs a format change or not.

anyway, i figure i need an IF stmt added to check if the cell is NULL value at this point, and skip over that iteration in the loop if it is, but wasnt sure how to achieve that on the fly because i barely get by with arrays in .NET (yeah arrays+me=dont get along well)

so i posted more details to clarify.. sorry, i should've taken the time the first attempt.

Many huge thanks for your assistance, i sincerely didn't mean to sound unappreciative!