date format problem from opening dbf file in excel 2007

  • Thread starter Thread starter pol
  • Start date Start date
P

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
 
Pol,

Not certain what is causing this in 2007, but you can standardize those
dates in another column using the text function.
 
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
 
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.
 
Thanks it was helpful to me

thanks a lot

macropod said:
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]


pol said:
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
Click to expand...
Click to expand...
 
Back
Top