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)?

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.

DJ wrote:

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

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

"DJ" wrote in message
...
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)?

Hi!

Try this:

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

Biff

"DJ" wrote in message
...
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)?

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)))

"DJ" wrote:

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)?

"DJ" wrote in message
...
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
--
David Biddulph

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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=551220