how to convert a column of numbers (monthdayyear) into dates?
 

At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006).
None of my attempts resulted in excel recognizing as valid dates.

how to convert a column of numbers (monthdayyear) into dates?
 

=IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2))))

Vaya con Dios,
Chuck, CABGx3



"wsucougar" wrote:

At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006).
None of my attempts resulted in excel recognizing as valid dates.

how to convert a column of numbers (monthdayyear) into dates?
 

Hi,

try it

=if(len(b2)=7,date(right(b2,4),left(b2,1),mid(b2,2 ,2)),date(right(b2,4),left(b2,2),mid(b2,3,2)))

hth
regards from Brazil
Marcelo

"wsucougar" escreveu:

At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006).
None of my attempts resulted in excel recognizing as valid dates.

how to convert a column of numbers (monthdayyear) into dates?
 

On Tue, 15 Aug 2006 10:37:02 -0700, wsucougar
wrote:

At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006).
None of my attempts resulted in excel recognizing as valid dates.

Here's one method:

=DATE(MOD(A1,10^4),INT(A1/10^6),MOD(INT(A1/10^4),100))


--ron

how to convert a column of numbers (monthdayyear) into dates?
 

Sorry, forgot the final case of the cell being anything other than containing
7 or 8 characters...........

=IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2)),""))

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

=IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2))))

Vaya con Dios,
Chuck, CABGx3



"wsucougar" wrote:

At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006).
None of my attempts resulted in excel recognizing as valid dates.

how to convert a column of numbers (monthdayyear) into dates?
 

Thanks for your help it was just what I needed and it works great!

"CLR" wrote:

Sorry, forgot the final case of the cell being anything other than containing
7 or 8 characters...........

=IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2)),""))

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

=IF(LEN(A1)=8,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3 ,2)),IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID( A1,2,2))))

Vaya con Dios,
Chuck, CABGx3



"wsucougar" wrote:

At issue is some numbers are 7 digits and some 8 (ie 7012006 or 10012006).
None of my attempts resulted in excel recognizing as valid dates.