formating a date field
 

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.

formating a date field
 

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.

formating a date field
 

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.

formating a date field
 

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