converting 8-digit number to date

[Guest]

I have been given an Excel spreadsheet with numbers in 8-digit text format.
For example, 20020812 would be August 12, 2002. How do I convert these to
some sort of date field that will allow me to perform calculations (count
days from one date to the next)?

 

[Dave Peterson]

If they're in one column...

Select the range
data|text to columns
choose fixed width and remove any lines that excel guessed
choose Date (ymd)
and plop it in the same range as where you picked it up.

And format those (now real) dates the way you want.

 

[Don Guillett]

a formula
=DATEVALUE(LEFT(F6,4)&"/"&MID(F6,5,2)&"/"&RIGHT(F6,2))
do you need a macro?

 

[Biff]

Hi!

Try this:

Select the range in question.
Goto Data>Text to Columns
Click Next, Next
Select Date
From the drop down select YMD
Click Finish

Biff

 

[Guest]

Assuming that your number is in cell A1, this formula will return a date:

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

 

[David Biddulph]

I have been given an Excel spreadsheet with numbers in 8-digit text format.
For example, 20020812 would be August 12, 2002. How do I convert these to
some sort of date field that will allow me to perform calculations (count
days from one date to the next)?

Data/ Text to Columns/ Fixed Width
Select output as Date/ YMD

 

[daddylonglegs]

Text to columns would appear to be the best approach, although if yo
did want a formula here's a simple one

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