formating a date field

S

Stephen

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.
 
D

Dave O

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.
 
Top