Macro to change date format

[MalibuLX3]

Hello Excel Friends,

I've been searching and trying to figure this out in Excel all day, and I just can't get what I need! So I hope you guys can help me figure it out.

Basically, I've got a column with a date, but it's not in a typical date format. I need a line in my macro to convert this to a typical date format so I can sort oldest to newest.

I've attached a sheet with "CURRENT" and "DESIRED" formats. Any help would be much appreciated, as I've got 100k+ columns to convert!

date.xlsx

THANKS!!

[JieJenn]

Why not use formula?

=TEXT(LEFT(A2,FIND("+",A2)-1), "mm/dd/yyyy") &" " &TEXT(ROUNDUP(LEFT(A2,FIND("+",A2)-1),3),"hh:mm") and drag it down

[MalibuLX3]

Why not use formula?

=TEXT(LEFT(A2,FIND("+",A2)-1), "mm/dd/yyyy") &" " &TEXT(ROUNDUP(LEFT(A2,FIND("+",A2)-1),3),"hh:mm") and drag it down

Thanks for reply. However, that formula ends up giving me the year as 2013 for all, when it could be 2009, 2010, 2011, or 2012. A macro would be best, as I'm building this into a large process. However, I'm new to macros, so I'm not exactly sure what is best.

Thanks!

[oeldere]

Non VBA solution (a part off)

See the attached file.

I used text to column to separate the data.

I used VLookup to change the month.

After that I combined the data and changed the format in dd-mm-yyyy

The time can also be added in the formula (if wanted).

You can copy paste special to get the data and delete the columns A -F.

Of course you can record the manual actions to get the VBA code.

[FDibbins]

Im not sure about a macro to do this, but the following foemula will give you what you want...

=DATE(VALUE(RIGHT(A2,4)),MONTH(DATEVALUE(LEFT(A2,15))),DAY(DATEVALUE(LEFT(A2,15))))+TIMEVALUE(MID(A2,8,8))
copied down

format custom m/d/yyyy h:mm

[jeffreybrown]

Borrowing the formula from post #5, For a macro, let me know how this works for you...

Please Login or Register  to view this content.

[HSV]

In The Netherlands it looks to me like this.
I changed the formule into this.

I do not know if the language matters.

Please Login or Register  to view this content.