Converting Data to Date Type from General Type

tanda · Feb 19th, 2002, 08:46 PM

I have imported data that is formatted as General. I need it formatted as Date. The form is 20010207 whcih represents February 7, 2001. The Format, Cells option to format as a Date returns #############, etc.

I need to be able to convert/format to a Date type in VBA in order to do some calculations with the dates (for example, use one date and sort remainders for those within 60 days). This process will be done in VBA code.

I cannot get the cell values recognized as Date types in any way.

Any help would be appreciated.

IML · Feb 19th, 2002, 08:51 PM

Highlight your column and try using text to columns under the data menu. When the wizard comes up, hit next, next again, then click on the date radio dial. Select YMD if your desired result is feb 7, or YDM if it is July 2. Finally hit finish. good luck

NateO · Feb 19th, 2002, 08:54 PM

Try the following (been there):

Choose Text to Columns from the Data Menu
When the Text Import Wizard appears click Next twice.
Change the Column Data Format to date and choose your desired format from the drop down control.
Choose Finish.

Incidentally, you can record this in a macro...

Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-19 19:59 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 19th, 2002, 08:46 PM	#1
tanda New Member Join Date: Feb 2002 Posts: 13	I have imported data that is formatted as General. I need it formatted as Date. The form is 20010207 whcih represents February 7, 2001. The Format, Cells option to format as a Date returns #############, etc. I need to be able to convert/format to a Date type in VBA in order to do some calculations with the dates (for example, use one date and sort remainders for those within 60 days). This process will be done in VBA code. I cannot get the cell values recognized as Date types in any way. Any help would be appreciated.

Feb 19th, 2002, 08:51 PM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	Highlight your column and try using text to columns under the data menu. When the wizard comes up, hit next, next again, then click on the date radio dial. Select YMD if your desired result is feb 7, or YDM if it is July 2. Finally hit finish. good luck

Feb 19th, 2002, 08:54 PM	#3
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Try the following (been there): Choose Text to Columns from the Data Menu When the Text Import Wizard appears click Next twice. Change the Column Data Format to date and choose your desired format from the drop down control. Choose Finish. Incidentally, you can record this in a macro... Cheers, Nate [ This Message was edited by: NateO on 2002-02-19 19:59 ]