Date conversion/sorting problem

frankpat · Apr 24th, 2002, 01:55 PM

I have a voluminous spreadsheet that I need to sort by date. The problem is how the dates are presented. For example: January 2, 2002 is presented as 1022002. October 2, 2002 is presented as 10022002. Essentially, dates are entered as integers, not in a form which Excel recognizes as dates. Do you know of a way that I could convert the integers to dates?
Thanks

Any assistance would be greatly appreciated.

Frank Patrinicola

Mark W. · Apr 24th, 2002, 02:07 PM

For text representation of date values listed in column A begining at cell A1 enter the formula...

=TEXT(A1,"0-00-0000")+0

...into B1 and fill down. You can then use Copy/Paste Special... Values to overlay the column A values.

...Or, just sort on the new column B values.

[ This Message was edited by: Mark W. on 2002-04-24 13:08 ]

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

Apr 24th, 2002, 01:55 PM	#1
frankpat New Member Join Date: Apr 2002 Posts: 4	I have a voluminous spreadsheet that I need to sort by date. The problem is how the dates are presented. For example: January 2, 2002 is presented as 1022002. October 2, 2002 is presented as 10022002. Essentially, dates are entered as integers, not in a form which Excel recognizes as dates. Do you know of a way that I could convert the integers to dates? Thanks Any assistance would be greatly appreciated. Frank Patrinicola

Apr 24th, 2002, 02:07 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	For text representation of date values listed in column A begining at cell A1 enter the formula... =TEXT(A1,"0-00-0000")+0 ...into B1 and fill down. You can then use Copy/Paste Special... Values to overlay the column A values. ...Or, just sort on the new column B values. [ This Message was edited by: Mark W. on 2002-04-24 13:08 ]