date conversion formula help

[bhmcpfs]

Attemptin to convert dates out of a large file in the format:

50610
52210
52810

To a recognizable date
5/6/2010
5/22/2010
5/28/2010

Here's my formula, so far:

=IF(D2>0,DATE(MOD(D2,100),TRUNC(D2/10000),TRUNC(MOD(D2,10000)/100))," ")

Data is in column D.

This works, kinda. Worked on dates prior to 1999 (95% of the dates). 2000 forward the formula fails, making 50610 5/6/1910 instead of 5/6/2010.

Have tried numerous variations with no luck so far... Any help/guideance would be greatly appreciated. Thanks in advance.

Mac

 

[T. Valko]

Attemptin to convert dates out of a large file in the format:

50610
52210
52810

To a recognizable date
5/6/2010
5/22/2010
5/28/2010

Here's my formula, so far:

=IF(D2>0,DATE(MOD(D2,100),TRUNC(D2/10000),TRUNC(MOD(D2,10000)/100))," ")

Data is in column D.

This works, kinda. Worked on dates prior to 1999 (95% of the dates). 2000 forward the formula fails, making 50610 5/6/1910 instead of 5/6/2010.

Have tried numerous variations with no luck so far... Any help/guideance would be greatly appreciated. Thanks in advance.

Mac

This worked for me...

Book1
	A	B
2	50610	5/6/2010
3	52210	5/22/2010
4	52810	5/28/2010
5	10192	1/1/1992
6	122200	12/22/2000
7	110588	11/5/1988
Sheet1

This formula entered in B2 and copied down:

=--TEXT(A2,"0-00-00")

Format as Date

 

[Jonmo1]

How about a non formula approach.
Provided the dates are consistent, and the DAY is always in 2 digit format 06 or 28 etc..

Highlight the column of dates
Data - Text To Columns
Delminated - Next
Uncheck all delimiters - Next
Choose Date - MDY
Finish

Hope that helps.

 

[bhmcpfs]

You're awesome! Works like a charm.

And WAY clearer than my DATE/MOD/TRUNC/TRUNC/MOD beast.

Thanks a bunch!

 

[bhmcpfs]

jonmo - Biff beat you to it, but I'd think your solution would work jus about as easily. To be honest, I wasn't thinking text-to-columns at all. Thanks also.

 

[T. Valko]

You're awesome! Works like a charm.

And WAY clearer than my DATE/MOD/TRUNC/TRUNC/MOD beast.

Thanks a bunch!

You're welcome. Thanks for the feedback!

 

[T. Valko]

jonmo - Biff beat you to it, but I'd think your solution would work jus about as easily. To be honest, I wasn't thinking text-to-columns at all. Thanks also.

I didn't think of that either!

Good thinking by jonmo.

 

[Jonmo1]

Glad to help out, thanks for the feedback..