Entering text, convert to date

**polishmf** · 03-12-2007, 10:30 AM

Hello,

I've searched the forums, and I've found some good tricks and tips, but I didn't seem to find the exact answer I was looking for.

I have a simple spreadsheet where users enter data. Name of company, address, and then a date at the end.

For the date column, to try and speed things up, I am trying to develop a function that will convert a date entered with no slashes into a proper date format.

Example;

User types 03122007, Excel automatically formats that to 03/12/2007. I would rather not parse the text and add a slash after the 2nd value, and another slash after the 4th value. I would like this to be an actual date format that Excel (or even Access) would recognize.

This could either be an automatic format within that cell, or could use another "helper" column to format it.

Thanks in advance for any help or suggestions.

**starguy** · 03-12-2007, 11:22 AM

Originally Posted by polishmf

Hello,

I've searched the forums, and I've found some good tricks and tips, but I didn't seem to find the exact answer I was looking for.

I have a simple spreadsheet where users enter data. Name of company, address, and then a date at the end.

For the date column, to try and speed things up, I am trying to develop a function that will convert a date entered with no slashes into a proper date format.

Example;

User types 03122007, Excel automatically formats that to 03/12/2007. I would rather not parse the text and add a slash after the 2nd value, and another slash after the 4th value. I would like this to be an actual date format that Excel (or even Access) would recognize.

This could either be an automatic format within that cell, or could use another "helper" column to format it.

Thanks in advance for any help or suggestions.

Hi
you can use helper column at the end of date column.
suppose you enter date in column C then put this function in col D
=IF(C1<>"",LEFT(C1,2)&"/"&MID(C1,3,2)&"/"&RIGHT(C1,4),"") and copy down.
when you would have entered all dates in column C then copy column D and paste special as values on column C. (i-e overwrite column C)
hope this would help you.

**polishmf** · 03-12-2007, 11:48 AM

Thanks starguy. That may work.

Is there anyway to format the cell itself to accept an entry of 8 digits and convert it to a date?

Example;

In B1, 03122007 is entered, and the user hits tab to move to the next field. B1 is then automatically formatted to 03/12/2007.