date formats

Lenny · Apr 2nd, 2002, 10:42 PM

I am importing data into excel from a Computer maintenance management system. The problem is the data that is imported is in a format excel doesn't recongnize. This is the result of a "year 2000 bug fix". The dates are in this format:

991023 : which is Oct. 23, 1999
1001023: which is Oct. 23, 2000
1011023: which is Oct. 23, 2001
1020403: which is Apr. 3, 2002
How can I make excel put this into a standard format such as 10/23/99, or Oct/23/01. Please help.

Jay Petrulis · Apr 2nd, 2002, 10:54 PM

Quote:

On 2002-04-02 21:42, Lenny wrote:
I am importing data into excel from a Computer maintenance management system. The problem is the data that is imported is in a format excel doesn't recongnize. This is the result of a "year 2000 bug fix". The dates are in this format:

991023 : which is Oct. 23, 1999
1001023: which is Oct. 23, 2000
1011023: which is Oct. 23, 2001
1020403: which is Apr. 3, 2002
How can I make excel put this into a standard format such as 10/23/99, or Oct/23/01. Please help.

Hi,

Data in column A
Cell A1 being tested here.

=DATE(LEFT(A1,LEN(A1)-4)+1900,MID(A1,LEN(A1)-3,2),RIGHT(A1,2))

and copy down the your list.

This should work for you.

Regards,
Jay

Dave Hawley · Apr 2nd, 2002, 11:19 PM

Hi Lenny

How about

=DATEVALUE(MID(A2,3,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,2))

And format the cell as the date format needed.

Lenny · Apr 2nd, 2002, 11:45 PM

Works great Jay. Thank you very much.

Regards, Lenny

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

Apr 2nd, 2002, 10:42 PM	#1
Lenny New Member Join Date: Apr 2002 Posts: 9	I am importing data into excel from a Computer maintenance management system. The problem is the data that is imported is in a format excel doesn't recongnize. This is the result of a "year 2000 bug fix". The dates are in this format: 991023 : which is Oct. 23, 1999 1001023: which is Oct. 23, 2000 1011023: which is Oct. 23, 2001 1020403: which is Apr. 3, 2002 How can I make excel put this into a standard format such as 10/23/99, or Oct/23/01. Please help.

Apr 2nd, 2002, 11:19 PM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Lenny How about =DATEVALUE(MID(A2,3,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,2)) And format the cell as the date format needed.

Apr 2nd, 2002, 11:45 PM	#4
Lenny New Member Join Date: Apr 2002 Posts: 9	Works great Jay. Thank you very much. Regards, Lenny