Macro to convert Text to Date format

[akynyemi]

Hi Guys,

I need help to convert Text to Date format automatically. I have a data validation where Month-Day-Year is picked and submitted but the submitted data does not have a date format except if it is double clicked. The data comes in "22-Mar-13" format but excel does not recognize it as a date until i double click it.

Thanks.

[HSV]

Hello,

Did you calculate automatically enabled in the options of Excel?

[Jakobshavn]

This Event macro works on column A:

Please Login or Register  to view this content.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

[akynyemi]

Hi Jakobshavn,

Thanks for your Event Macro, I installed it on my workbook but the dates still do not get converted from text to dates still.

Please find attached a sample from my workbook. Sheet 1 shows MONTH, DAY and YEAR where any combination of dates can be chosen at random and submitted. On submission, data goes to sheet 2 but does not display as date which is where I need help.

Thank you for your time.Sample.xlsx

[HSV]

Hello akynyemi,

I hope it works in your Excel version.

Please Login or Register  to view this content.

[akynyemi]

Hi Harry,

Thanks for your response. I have a Macro in place that saves data from one worksheet to another in the same workbook. One of the data saved is a date where Month, Day and Year can be selected at random (use of data validation), the Macro combines the three and pastes in another worksheet. The issue I have is that the saved dates are not formatted as dates but as text until i doubleclick. Is there a way I can have a macro automatically change the formatting to dates for me?

Thanks.

[Jakobshavn]

Perhaps if you posted the macro the creates the dates, we could fix it

[akynyemi]

Hi Jakobshavn,

Thanks for the swift response. Please see macro below. A6:C6 contains the date combination and automatically gets saved to another sheet with the rest of the data.

Please Login or Register  to view this content.

[HSV]

Would this help?

Please Login or Register  to view this content.

[akynyemi]

Hi,

It still didnt work. Attached is an excel file with only one sheet. In column A2 is a date in a date format, but A3 downwards have dates in text formats. Double clicking any of them and pressing enter would convert the text formats into date formats. I was able to write two different macros below, to do this but the text formats were still not converted to date formats. Kindly assist in taking a look again.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Jakobshavn]

Try this:

Please Login or Register  to view this content.

[akynyemi]

Hi Jakobshavn,

It still doesn't work. The macro does not run past the 1st row.

Please find attached.

[Jakobshavn]

My Fault!!:

Please Login or Register  to view this content.

I forgot the Select within the loop!

[akynyemi]

Hi Jakobshavn,

This time it runs through the data but A3:A24 does not get formatted to the date format as A2 after formatting with Send Keys{F2} and pressing enter with the macro.

[Jakobshavn]

If A2 has the desired format then we will apply it to the column:

Please Login or Register  to view this content.