DATE problem

Posted by Naveen on August 15, 2001 8:32 AM

Hi,

Could anyone help me with this? I have a Date field with values of the format 3/25/98, 10/13/98 ...

But when I do the Insert statement(which I pasted below), the Date value changes to 35879 for 3/25/98. I think it is taking the text value of the date. But, how do I display the date as it is. I even tried changing the date field (in this case, it is cell D) to a date format. But, it did not work.

=CONCATENATE("INSERT into Proj VALUES (", A2,",'",B2,"','",C2,"','",D2,"','",E2,"','",F2,"','",G2,"','",H2,"',",I2,",'",J2,"',",K2,",",L2,",'",M2,"','",N2,"'...........

Thanks for the help.

Naveen

Posted by Mark W. on August 15, 2001 8:41 AM

Use =TEXT(D2,"m/dd/yy")

Posted by Naveen on August 15, 2001 9:04 AM

Thanks Mark for your suggestion. I tried this but it gives an error.

=CONCATENATE("INSERT into Proj VALUES (", A2,",'",B2,"','",C2,"','",TEXT(D2,"m/dd/yy")"','",E2,"','",F2,"','",G2,"','",H2,"',",I2,",'",J2,"',",K2,",",L2,",'",M2,"','",N2,"'.......

Naveen

Posted by Mark W. on August 15, 2001 10:07 AM

=CONCATENATE("'",TEXT(B1,"mm/dd/yy"),"'") works
just fine!!! I bet you are missing a quote (")
somewhere. What's the error message?

Posted by Naveen on August 15, 2001 11:41 AM

Thank you once again Mark. I was missing ',' after ')'. It works fine now.

The new statement looks like this:

=CONCATENATE("INSERT into Proj VALUES (", A2,",'",B2,"','",C2,"','",TEXT(D2,"m/dd/yy"),"','",E2,"','",F2,"','",G2,"','",H2,"',",I2,",'",J2,"',",K2,",",L2,",'",M2,"','",N2,"','",O2)
and the formula continues from P2 to AB2 in the next cell.

Thanks,

Naveen