Date view problem. When .dbf file open with excel 2003 & 2007

[AVK]

If i have open .dbf file open with excel 2007.
After opening file some date show properly but some date are not show properly.
for example : 20130913 instead of 13/09/2013. I have attach both version file for your ready reference. Please see row 240 to 264 , 276 to 317 & so on..

But same file if i have open with excel 2003 all dates show properly.

In view of the above plz. let me know the reason & if any solution plz. solve above problem.

 

[Aurelie]

Hi Atul,

The problem is that some dates are stored as text, therefore Excel does not know how to convert it into date, and just converting the string into value does not work.
Enter the following formula in the cell C240 (for example): =DATEVALUE(CONCATENATE(RIGHT(B240,2),"/",MID(B240,5,2),"/",LEFT(B240,4))) and drag down. If the format of the cell is on general, you should see 41530, which is just the reference number of 13/09/2013. Then change the format of the cells to Date and you should show you what you are looking for.

Please let me know if it works or not.

 

[SirJB7]

Hi, atul khandekar!
You can avoid using CONCATENATE function and make Aurelie's formula a bit shorter:
=DATEVALUE(RIGHT(B240,2)&"/"&MID(B240,5,2)&"/"&LEFT(B240,4))
And borrowing her formula without permission you could use the following one to build a new unique column of proper dates:
=SI(Y(B2+1=B2+1;B2<100000);B2;FECHANUMERO(DERECHA(B2;2)&"/"&EXTRAE(B2;5;2)&"/"&IZQUIERDA(B2;4))) -----> in english: =IF(AND(B2+1=B2+1,B2<100000),B2,DATEVALUE(RIGHT(B2,2)&"/"&MID(B2,5,2)&"/"&LEFT(B2,4)))
Regards!

 

[Aurelie]

@SirJB7,
Please feel free to steal any of my formulas (or borrow it without permission, as you wish ), especially if it is to improve them! At least I learned again something new...

What about: =IF(ISNUMBER(B2),B2,DATEVALUE(RIGHT(B2,2)&"/"&MID(B2,5,2)&"/"&LEFT(B2,4))) to simplify the first test? (on condition that the cells were not formatted to any other format...)

Cheers,

 

[SirJB7]

@Aurelie
Hi!
First, thanks for the given and yet taken permission, I love stealing so as to work less, I love much indeed leisure than working.
In this case your formula works fine and it's simpler than mine, but because I don't trust -sometimes not even a bit- on users, what if appeared cells with YYYYMMDD but as numbers and not strings? Yeah, just a little paranoid, but a few chars more and I'd get rid of those creative users.
Regards!

 

[Aurelie]

@SirJB7 Hi,
Fair enough, you are definitely right, but I have to admit I still lack of experience. I always try to keep it simple where possible, but I must admit that creative users, as you poetically call them, can be unpredictable and therefore using a formula which would work in any case is probably wiser...

Cheers,

 

[Marc L]

Hi atul,​

​

I open your .xls file on Excel 2003 and save as a new .dbf file.​

I open this .dbf file on another computer with Excel 2007 : no problem occurs !​

​

So the easy way is to correct the source .dbf file with real dates …​

​

 

[AVK]

Dear Aurelie, Thanks for reply, But my question is why problem create in Excel 2007 & why not in Excel 2003? I don't require any formula. Is it possible without formula.

 

[AVK]

Dear Marc
Date showing problem create if original .dbf file open with Excel 2007. But same .dbf file open with Excel 2003 problem not created.

 

[Marc L]

​

So as I said, the problem belongs to the original .dbf file which has some no real dates …​

​

 

[SirJB7]

@SirJB7 Hi,
Fair enough, you are definitely right, but I have to admit I still lack of experience. I always try to keep it simple where possible, but I must admit that creative users, as you poetically call them, can be unpredictable and therefore using a formula which would work in any case is probably wiser...

Cheers,

@Aurelie
Hi!
Thanks for ack but it's nothing related to experience (my father used to say that experience was a comb that you got when you've yet become bald), it's mostly a mix between suspicion, mistrust and paranoia... of course all of them induced by the damned creative users.
Learning process it's just a matter of read, practice, try, fail, retry, ... until succeed. It happens to me all the time, and I hope it'd happen for a long time. And always remember Albert Einstein quote: "We are all very ignorant, what happens is that not all ignore the same things".
Regards!

 

[AVK]

Dear Marc
In .dbf file all dates are real. As per date format only. Field Name: Tdate / Type : Date / Width : 8