Excel Datevalue function-convert 7000 cells

Maryken · May 13th, 2002, 01:58 PM

I have an Excel sheet with 7000 cells in column C -currently have a text value
14-DEC-99 etc.. I need to convert the entire column with this data to a real date format.
I tried to highlight first cell-go to formula bar and type =datevalue('14-dec-99')
and it does give me the serial value which I can then format with the date format 12/14/99..but when I do a copy from this cell to all the way down the column I can't get the correct values past approx.the 900th
row - it gives me #VALUE.
DO you know how to do this quick and easy to change these 7000 records in this C column to a date value???
Thanks,
Mary

Aladin Akyurek · May 13th, 2002, 02:04 PM

Copy an unused, empty cell.

Select the range of text-dates.

Activate Edit|Paste Special >Add.

Format the range (now filled up with numbers) as date.

Mark W. · May 13th, 2002, 02:45 PM

Select the cells containing the text representation of dates, choose the Data | Text to Columns... menu command, and press [ Finish ]. Format the resultant datevalues as desired.

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

May 13th, 2002, 01:58 PM	#1
Maryken New Member Join Date: May 2002 Posts: 1	I have an Excel sheet with 7000 cells in column C -currently have a text value 14-DEC-99 etc.. I need to convert the entire column with this data to a real date format. I tried to highlight first cell-go to formula bar and type =datevalue('14-dec-99') and it does give me the serial value which I can then format with the date format 12/14/99..but when I do a copy from this cell to all the way down the column I can't get the correct values past approx.the 900th row - it gives me #VALUE. DO you know how to do this quick and easy to change these 7000 records in this C column to a date value??? Thanks, Mary

May 13th, 2002, 02:04 PM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Copy an unused, empty cell. Select the range of text-dates. Activate Edit\|Paste Special >Add. Format the range (now filled up with numbers) as date.

May 13th, 2002, 02:45 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Select the cells containing the text representation of dates, choose the Data \| Text to Columns... menu command, and press [ Finish ]. Format the resultant datevalues as desired.