Date problem

[JohnR]

I get a database from a client which has employees' dates of birth and date
of hire on it. The client uses an IBM A/S 400 for their systems. The
programmer sends me the data in a CSV format which is fine.

The date is coded in a yymmdd format. For example, for someone born Nov. 25,
1974, I have this string of numbers: 741125. Using either Excel XP or Excel
97, when I try and convert this to a date using custom cell formating, I get
really weird dates. The above date comes out as 2/16/3929.

I could hand convert all the dates but with 1000 employees this would get
tedious.

Any suggestions on how to convert this to something useful so I can search
the DOB's?

Thanks

-John

 

[Ron Rosenfeld]

I get a database from a client which has employees' dates of birth and date
of hire on it. The client uses an IBM A/S 400 for their systems. The
programmer sends me the data in a CSV format which is fine.

The date is coded in a yymmdd format. For example, for someone born Nov. 25,
1974, I have this string of numbers: 741125. Using either Excel XP or Excel
97, when I try and convert this to a date using custom cell formating, I get
really weird dates. The above date comes out as 2/16/3929.

I could hand convert all the dates but with 1000 employees this would get
tedious.

Any suggestions on how to convert this to something useful so I can search
the DOB's?

Thanks

-John

Assuming that none of the dates are any later than this year, you could use
this formula:


=DATE(INT(A1/10^4)+1900+100*(A1<MOD(YEAR(TODAY()),100)*10^4),MOD(INT(A1/100),100),MOD(A1,100))


--ron

 

[JohnR]

This should work for dates of birth but won't work for dates of hire. How
would I modify this to work with stuff in years 2000 and beyond?

-John

PS: Lest I forget - thanks for responding!

 

[Peo Sjoblom]

Select the date, do data>text to columns, click next twice, under column
data format select Date and YMD
and click Finish

 

[JohnR]

I went in and used your formula and it worked perfectly. Thanks.

 

[Ron Rosenfeld]

Select the date, do data>text to columns, click next twice, under column
data format select Date and YMD
and click Finish

Since he's also dealing with dates of birth, if he uses this technique, he may
have to make the appropriate changes (depending on his OS) to allow Dates prior
to 1930. Otherwise something like 251101 will convert to 11/01/2025.


--ron

 

[Ron Rosenfeld]

I went in and used your formula and it worked perfectly. Thanks.

As written, so long as the date is not future dated to the following year, the
formula should work OK. If you do have a future dated date, post back and I
can easily change the formula. It's just a matter of changing the
YEAR(TODAY()) function. I did it this way not knowing what your ranges are.
But the number could just as easily be hard-coded.


--ron

 

[JohnR]

Ron,

I don't have a future dated date. I did notice one quirk using your formula
on the dates in Excel 97. It handled all years except for 2004 correctly. It
shows 2004 dates as 1904. What is causing that?

-John

 

[Ron Rosenfeld]

Ron,

I don't have a future dated date. I did notice one quirk using your formula
on the dates in Excel 97. It handled all years except for 2004 correctly. It
shows 2004 dates as 1904. What is causing that?

-John

Easy fix. As I said, I wasn't sure of your date range. Use this modification:


=DATE(INT(A1/10^4)+1900+100*(A1<(1+MOD(YEAR(TODAY()),100))*10^4),MOD(INT(A1/100),100),MOD(A1,100))

It would be simpler if you had an "earliest possible date". Then the number
could be hard-coded.


--ron

 

[JohnR]

Ron,

I appreciate all the help. Many thanks.

-John