date format problem from opening dbf file in excel 2007

[pol]

Hai all,

When I am opening a dbf file through excel 2007 the datcolumn shows as
follows. But there is no problem while opening through old version . How I
can solve this problem in 2007 excel

Date

20080118
20080118
20080118
20080125
20080125
20080125
20080125
02/12/2008
02/12/2008
02/12/2008
02/12/2008
02/12/2008
20080214
20080214
20080214
20080214
04/04/2008
04/04/2008
04/04/2008
04/04/2008
20081017
20081017
20081017

With thanks and regards

Pol

 

[galimi]

Pol,

Not certain what is causing this in 2007, but you can standardize those
dates in another column using the text function.

 

[pol]

Please let me know how to use text function to solve this problem

With thanks
Pol

 

[Pete_UK]

Assuming your "dates" are in column D, starting in D2, then you can
put this formula in an adjacent column on row 2:

=IF(D2<40000,D2,DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2)))

Format the cell as a date in the style you prefer, and then copy down.

Hope this helps.

Pete

 

[macropod]

Hi pol,

Assuming the data start in D2, try:
=IF(LEFT(CELL("format",D2),1)="D",D2,DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2)))
Format the cell as a date, then copy down.

 

[pol]

Thanks it was helpful to me

thanks a lot

Hi pol,

Assuming the data start in D2, try:
=IF(LEFT(CELL("format",D2),1)="D",D2,DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2)))
Format the cell as a date, then copy down.
--
Cheers
macropod
[MVP - Microsoft Word]

Hai all,

When I am opening a dbf file through excel 2007 the datcolumn shows as
follows. But there is no problem while opening through old version . How I
can solve this problem in 2007 excel

Date

20080118
20080118
20080118
20080125
20080125
20080125
20080125
02/12/2008
02/12/2008
02/12/2008
02/12/2008
02/12/2008
20080214
20080214
20080214
20080214
04/04/2008
04/04/2008
04/04/2008
04/04/2008
20081017
20081017
20081017

With thanks and regards

Pol