Changing text to date format
 

Hi

Using Excel 2003.

I have a long list of dates in the format 14.6.61. These are currently
aligned to the left and are considered to be text. Changing the format
to date doesn't appear to make any changes. The column begins at D1
with the word 'DOB'.

How can change these data to date format?

Thanks

Changing text to date format
 

Select that column
Data|Text to columns
fixed width (but remove any lines that excel guessed)
Tell excel that the field is a date in dmy order

And finish up.

CM wrote:

Hi

Using Excel 2003.

I have a long list of dates in the format 14.6.61. These are currently
aligned to the left and are considered to be text. Changing the format
to date doesn't appear to make any changes. The column begins at D1
with the word 'DOB'.

How can change these data to date format?

Thanks

--

Dave Peterson

Changing text to date format
 

This datte is a vvery hard date to convert becaue the number of characters
are not fixed at two characters for each filed (day, month, year). to use an
excel fformula is nearly impossible becasue it is so complex. Instead use a
VBA function. the one below works. You have tto format the cell on the
Excel spreadsheet as a Date (any date format you like), otherwise you get a
number that doesn't make any sense.

Function Convertdate(Stringdate As String) As Date

MyStringdate = Stringdate
FirstPeriod = InStr(MyStringdate, ".")
Myday = Left(MyStringdate, FirstPeriod - 1)
MyStringdate = Mid(MyStringdate, FirstPeriod + 1)
SecondPeriod = InStr(MyStringdate, ".")
MyMonth = Left(MyStringdate, SecondPeriod - 1)
MyYear = Mid(MyStringdate, SecondPeriod + 1)

Stringdate = MyMonth + "/" + Myday + "/" + MyYear

Convertdate = CDate(Stringdate)
End Function


"CM" wrote:

Hi

Using Excel 2003.

I have a long list of dates in the format 14.6.61. These are currently
aligned to the left and are considered to be text. Changing the format
to date doesn't appear to make any changes. The column begins at D1
with the word 'DOB'.

How can change these data to date format?

Thanks