Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell.Text different from Cell.Value when dates are concerned...
A company I work for is using Excel to export and import data into other
parts of the program. User A will export data into an Excel file and give it to User B who then imports that Excel file into their database. Since there are several Memo/Text fields in the data, the entire export is done doing automation writing each field's value cell by cell. Recently, we have discovered that in Excel 2007, although the Cell's Text field say 1/1/1900, when we query the Value property of that cell it reads as 12/31/1899 12:00:00 am. I could understand the date-time portion of this, but why does Excel subtract an entire day from the date specified in the Text? (Please note that the export simply sets the Cell's Text property without any further consideration to formatting to simulate the way in which a user would type into the Cell) I'm thinking this is a definite bug in Excel, but do not know how to report it to Microsoft. I also find it hard to believe that I would be the first person to discover this issue, so any help would be greatly appreciated.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell.Text different from Cell.Value when dates are concerned...
Hi,
Well it is a bug but an intentional one. Excel followed on from Lotus 123 and in Lotus there was a genuine bug where it treated 1900 as a leap year when in fact is wasn't. In order to allow migration from Lotus to Excel without the need to change dates, Microsoft continued with the bug and do so to this day. VB displays the correct date for the value which is 1 day earlier. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Travis Vandersypen" wrote: A company I work for is using Excel to export and import data into other parts of the program. User A will export data into an Excel file and give it to User B who then imports that Excel file into their database. Since there are several Memo/Text fields in the data, the entire export is done doing automation writing each field's value cell by cell. Recently, we have discovered that in Excel 2007, although the Cell's Text field say 1/1/1900, when we query the Value property of that cell it reads as 12/31/1899 12:00:00 am. I could understand the date-time portion of this, but why does Excel subtract an entire day from the date specified in the Text? (Please note that the export simply sets the Cell's Text property without any further consideration to formatting to simulate the way in which a user would type into the Cell) I'm thinking this is a definite bug in Excel, but do not know how to report it to Microsoft. I also find it hard to believe that I would be the first person to discover this issue, so any help would be greatly appreciated.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell.Text different from Cell.Value when dates are concerned...
It is a known bug in Excel which prevents dates from 1/1/1900 through
2/28/1900 from being calculated properly. You just need to adjust by 1. Lotus 1-2-3 incorrectly treated 1900 as a leap year and for compatibility reasons this error was maintained so... -- Jacob (MVP - Excel) "Travis Vandersypen" wrote: A company I work for is using Excel to export and import data into other parts of the program. User A will export data into an Excel file and give it to User B who then imports that Excel file into their database. Since there are several Memo/Text fields in the data, the entire export is done doing automation writing each field's value cell by cell. Recently, we have discovered that in Excel 2007, although the Cell's Text field say 1/1/1900, when we query the Value property of that cell it reads as 12/31/1899 12:00:00 am. I could understand the date-time portion of this, but why does Excel subtract an entire day from the date specified in the Text? (Please note that the export simply sets the Cell's Text property without any further consideration to formatting to simulate the way in which a user would type into the Cell) I'm thinking this is a definite bug in Excel, but do not know how to report it to Microsoft. I also find it hard to believe that I would be the first person to discover this issue, so any help would be greatly appreciated.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell.Text different from Cell.Value when dates are concerned..
I should have added that once past day 60 the 'bug' disappears and worksheets
and VB evaluate as the same -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Well it is a bug but an intentional one. Excel followed on from Lotus 123 and in Lotus there was a genuine bug where it treated 1900 as a leap year when in fact is wasn't. In order to allow migration from Lotus to Excel without the need to change dates, Microsoft continued with the bug and do so to this day. VB displays the correct date for the value which is 1 day earlier. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Travis Vandersypen" wrote: A company I work for is using Excel to export and import data into other parts of the program. User A will export data into an Excel file and give it to User B who then imports that Excel file into their database. Since there are several Memo/Text fields in the data, the entire export is done doing automation writing each field's value cell by cell. Recently, we have discovered that in Excel 2007, although the Cell's Text field say 1/1/1900, when we query the Value property of that cell it reads as 12/31/1899 12:00:00 am. I could understand the date-time portion of this, but why does Excel subtract an entire day from the date specified in the Text? (Please note that the export simply sets the Cell's Text property without any further consideration to formatting to simulate the way in which a user would type into the Cell) I'm thinking this is a definite bug in Excel, but do not know how to report it to Microsoft. I also find it hard to believe that I would be the first person to discover this issue, so any help would be greatly appreciated.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining dates and text in a cell | Excel Worksheet Functions | |||
Macro takes a while to run - should I be concerned? | Excel Programming | |||
select text in cell based on text from another cell, paste the text at the begining of a thrid cell, etc... | Excel Programming | |||
Text and dates into one cell without loosing date format | Excel Discussion (Misc queries) | |||
Text AND dates in the same cell | Excel Programming |