converting 8-digit number to date

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)?
 
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.
 
a formula
=DATEVALUE(LEFT(F6,4)&"/"&MID(F6,5,2)&"/"&RIGHT(F6,2))
do you need a macro?
 
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
 
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 said:
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)?
Click to expand...

Data/ Text to Columns/ Fixed Width
Select output as Date/ YMD
 
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")+
 
Back
Top