Hi ,
In the database the date is 1/1/1900. But when I exactract to excel it is displaying it as 1/2/1900 (mm/dd/yyyy format).
Can anyone please let me know why it is displaying like this.
Thanks in Advance
Regards
Naveen
Hi ,
In the database the date is 1/1/1900. But when I exactract to excel it is displaying it as 1/2/1900 (mm/dd/yyyy format).
Can anyone please let me know why it is displaying like this.
Thanks in Advance
Regards
Naveen
I have no idea is this is teh cause, but check your excel options under Advanced/When calculating this workbook, and see if you have 1904 date system checked. If so, uncheck it
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
It is already unchecked (1904 system date) in options. But no idea it looks like is it issue with 1900 leap year? or is it something else
Regards
Naveen
Where is the data coming from, and what (exactly) does the imported date look like?
In Excel, dates are stored as integers, namely the number of days since 12/31/1899.
Thus, 1/1/1900 is the number 1, and 1/2/1900 is the number 2.
So, when you "extracted to Excel", the number 2 was transfered from the database into the Excel.
I cannot tell you how that happened because: (1) you do not say what database you used; and (2) you do not explain exactly how you "extracted to Excel".
FYI, in VBA, the date constant #1/1/1900# is the number 2, not 1. So if you have read 1/1/1900 from a database into a VBA type Date variable, then stored that variable into an Excel cell, Excel would display 1/2/1900. Demonstration:
[EDIT] VBA and Excel sync up on 3/1/1900. And yes, the reason is that Excel thinks 2/29/1900 is a leap day, whereas VBA does not.Please Login or Register to view this content.
Last edited by joeu2004; 10-25-2014 at 08:39 PM. Reason: typo: #1/1/1900# is 2, not #1/2/1900#
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks