I get the follow output from my datbase which is a date field.

7012005
12012005

I am trying to format it in 07/01/2005 fashion.
If I try to format it to a date stylein excel the dates do not convert
correctly. Any thoughts on how to get this format correctly.

You can use the DATE() function in Excel along with some string parsing
functions to do this, but that 7012005 is going to be a bit tricky- in
a best case scenario that would come across as 07012005. The 8 digit
07012005 is easier because some month numbers have 1 digit and some
have 2, and pulling the month number out of that string is easier if
the number of columns is standard.

I suspect your database output is sending those fields as numerics, and
Excel is stripping off the leading zero.

Assuming your database output is in cell A1:
=IF(LEN(A1)=7,DATE(RIGHT(A1,4),MID(A1,1,1),MID(A1, 2,2)),DATE(RIGHT(A1,4),MID(A1,1,2),MID(A1,3,2)))

Format the formula cell for date, and copy down the column as necessary.

PERFECT!!!! THANK YOU!!!!

It works very well.

Thanks again.

"Dave O" wrote:

You can use the DATE() function in Excel along with some string parsing
functions to do this, but that 7012005 is going to be a bit tricky- in
a best case scenario that would come across as 07012005. The 8 digit
07012005 is easier because some month numbers have 1 digit and some
have 2, and pulling the month number out of that string is easier if
the number of columns is standard.

I suspect your database output is sending those fields as numerics, and
Excel is stripping off the leading zero.

Assuming your database output is in cell A1:
=IF(LEN(A1)=7,DATE(RIGHT(A1,4),MID(A1,1,1),MID(A1, 2,2)),DATE(RIGHT(A1,4),MID(A1,1,2),MID(A1,3,2)))

Format the formula cell for date, and copy down the column as necessary.

You could also use this formula

=TEXT(A1,"00-00-0000")+0


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503384