date format problem from opening dbf file in excel 2007

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
 
G

galimi

Pol,

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

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
 
M

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

pol

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

date format problem 1
Find then highlight in yellow 6
Date / Time problem 4
Date problem 2
Problem sorting file dates in an array 2
How to reset incorrect date format after reinstalation of Window X 3
Obtain Month & Year from a Text formatted Date field using Left Fu 2
Outlook Connector Beta cannot retrieve mail prior to <install date 2

Top