another date format question

[Aaron_Tram]

I cannot find the answer within the form, but I may be overlooking it. There are many similar posts, most using =LEFT() =RIGHT(), etc.

I use the US version of Excel and have my date/time format in Windows set to US. I have a user (also US format) who inputs dates in the M.D.YY format. Unfortunately Excel does not recognize these as dates so using them for calculations is not possible. I would like to set up a macro to reformat the M.D.YY into a standard MM/DD/YYYY format or similar.

An example would be column A with:
4.24.13
4.24.13
4.24.13
4.24.13

I found the below macro which gets the month and date backwards, so it is not helpful but gets close...

Please Login or Register  to view this content.

Any help would be appreciated. Thanks!

[TMS]

The macro is using Text to Columns which is often the solution. You can record your own version.

Start recording a macro. Select the column of dates. Choose Data | Text to Columns. Choose Delimited. Clear all the selections and click OK. On the next page click on MDY format and then OK out. Stop the macro.

You should now have your own version of the macro.


Regards, TMS

[Aaron_Tram]

Thank you! I had never encountered that function in Excel. I had to adjust the macro such that the output would be in the appropriate place when I needed to use it in a different location. Here's what I did...

Please Login or Register  to view this content.

[TMS]

You're welcome.

By recording the macro yourself and amending the parameters to meet your needs you will have learned a lot.

Regards, TMS