Need Help Converting Numbers to Dates

[JWNJ]

I have been given an Excel file with that contains the installation date for
10,000 items. I need to determine the number of days/years these items have
been in-service. I know the appropriate formula to determine the number of
days between two dates; my problem is the installation date is formatted as
yyyymmdd. So, for example, today appears as 20070521. How can I convert
this is appear as 5/21/07, or some legitimate date format. Thanks in advance
for your help.

 

[Gary''s Student]

Try:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

[JWNJ]

Thanks gsnu. That worked.

 

[T. Valko]

I've been meaning to ask you....

gsnu200723

What does that mean?

I think I can figure out what gs means.

Biff

 

[Gord Dibben]

One more method without the formulas to deal with.

Data>Text to Columns>Next>Next>Column data format>Date YMD and Finish.


Gord Dibben MS Excel MVP

 

[Gary''s Student]

Hi Biff:

The gsnu is just a serial number I append to my username. I change it about
once a week. It assists me in locating old posts to which I may want to
refer. I use gsnuxx for VBA I may want to re-use.

 

[T. Valko]

That's a good idea!

Biff

 

[Harlan Grove]

. . . my problem is the installation date is formatted as
yyyymmdd. So, for example, today appears as 20070521. How can I
convert this is appear as 5/21/07, . . .

....

Another formula alternative

=--REPLACE(REPLACE(x,7,0,"-"),5,0,"-")