Date Conversion Help Wanted

[carltonb]

I have a database that provides me a date in text format.

ie: 06 APR 1895
07 NOV 2004 etc.

I need to convert it to read

04061895 and 11072004 respectively

I wrote a formula like this =TEXT(TRIM(A22),"mmddyyyy"), but it did no
convert the pre 1900 dates. I then wrote the following,
=LEFT(TRIM(A1),2)&TEXT(MONTH("11-"&MID(TRIM(A1),4,3)),"00")&RIGHT(TRIM(A1),4


but it gave me dates like 06041895 and 07112004 - DDMMYYYY

How do I change it to read MMDDYYYY. I tried formatting etc but no go.
Any help would be appreciated.
Carlton

 

[Dave Peterson]

The bad news is that excel doesn't like dates before Jan 1, 1900.

But maybe you could use John Walkenbach's xDate addin:
http://j-walk.com/ss/excel/files/xdate.htm

 

[Ron Rosenfeld]

I have a database that provides me a date in text format.

ie: 06 APR 1895
07 NOV 2004 etc.

I need to convert it to read

04061895 and 11072004 respectively

I wrote a formula like this =TEXT(TRIM(A22),"mmddyyyy"), but it did not
convert the pre 1900 dates. I then wrote the following,
=LEFT(TRIM(A1),2)&TEXT(MONTH("11-"&MID(TRIM(A1),4,3)),"00")&RIGHT(TRIM(A1),4)


but it gave me dates like 06041895 and 07112004 - DDMMYYYY

How do I change it to read MMDDYYYY. I tried formatting etc but no go.
Any help would be appreciated.
Carltonb

Because you specify dates prior to 1900, you will have to use Visual Basic.

The following UDF (user defined function) will convert dates as you specify.
The result of this conversion will be a text string and not a true Excel date.
Accordingly, you will not be able to perform calculations on the result without
taking that into effect. (However, VB would be required for any desired date
calculations anyway, since you have dates prior to 1900).

To enter this UDF:

<alt-F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer, then Insert/Module.
Paste the code below into the window that opens.

To use it, simply enter the formula =DtStr(A1) into some cell where A1 contains
your date.

As written, the formula displays nothing if A1 does not contain anything that
can be coerced to a date by VB. In Microsoft Windows, the range of valid dates
is January 1, 100 A.D. through December 31, 9999 A.D.

============================
Function DtStr(dt) As String

If IsDate(dt) Then
DtStr = Format(dt, "mmddyyyy")
End If


End Function
====================


--ron