|
wrong date returned on ws.Cells(r,c).value
I have writen a vb6 app that extracts user-entered data from a spreadsheet and loads it into an oracle database. It all works fine apart from some of the date values in the spreadsheet being returned incorrectly. For example when debugging the var strRowValue in the code snippet below, 01/01/1900 00:00 in the spreadsheet is returned as 31/12/1889 02/01/1900 00:00 in the spreadsheet is returned as 31/01/1900 01/05/2005 is correctly returned Set ws = wb.Worksheets(UCase(strTables(i))) strRowValue = ws.Cells(iRow, iCol).Value The spreadsheet uses the 1900 system and the format of the cells that the dates are held in is "Custom - 'dd/mm/yyyy hh:mm'" Any ideas would be very appreciated. Shawn -- sbvb ------------------------------------------------------------------------ sbvb's Profile: http://www.excelforum.com/member.php...o&userid=24604 View this thread: http://www.excelforum.com/showthread...hreadid=381924 |
wrong date returned on ws.Cells(r,c).value
? cdate(ActiveCell.Value)
12/31/1899 ? ActiveCell.Text 01/01/1900 0:00 not 1889 Excel only supports dates in the 20th century and later (VBA/VB is different). It counted 1900 as a leap year (supposedly to be compatible with a similar error in Lotus 1-2-3). so date prior to 1 Mar 1900 are off by one. -- Regards, Tom Ogilvy "sbvb" wrote in message ... I have writen a vb6 app that extracts user-entered data from a spreadsheet and loads it into an oracle database. It all works fine apart from some of the date values in the spreadsheet being returned incorrectly. For example when debugging the var strRowValue in the code snippet below, 01/01/1900 00:00 in the spreadsheet is returned as 31/12/1889 02/01/1900 00:00 in the spreadsheet is returned as 31/01/1900 01/05/2005 is correctly returned Set ws = wb.Worksheets(UCase(strTables(i))) strRowValue = ws.Cells(iRow, iCol).Value The spreadsheet uses the 1900 system and the format of the cells that the dates are held in is "Custom - 'dd/mm/yyyy hh:mm'" Any ideas would be very appreciated. Shawn -- sbvb ------------------------------------------------------------------------ sbvb's Profile: http://www.excelforum.com/member.php...o&userid=24604 View this thread: http://www.excelforum.com/showthread...hreadid=381924 |
wrong date returned on ws.Cells(r,c).value
Yes 1899, typo.. Thanks Tom that's sorted it out Tom Ogilvy Wrote: ? cdate(ActiveCell.Value) 12/31/1899 ? ActiveCell.Text 01/01/1900 0:00 not 1889 Excel only supports dates in the 20th century and later (VBA/VB is different). It counted 1900 as a leap year (supposedly to be compatible with a similar error in Lotus 1-2-3). so date prior to 1 Mar 1900 are off by one. -- Regards, Tom Ogilvy "sbvb" wrote in message ... I have writen a vb6 app that extracts user-entered data from a spreadsheet and loads it into an oracle database. It all works fine apart from some of the date values in the spreadsheet being returned incorrectly. For example when debugging the var strRowValue in the code snippet below, 01/01/1900 00:00 in the spreadsheet is returned as 31/12/1889 02/01/1900 00:00 in the spreadsheet is returned as 31/01/1900 01/05/2005 is correctly returned Set ws = wb.Worksheets(UCase(strTables(i))) strRowValue = ws.Cells(iRow, iCol).Value The spreadsheet uses the 1900 system and the format of the cells that the dates are held in is "Custom - 'dd/mm/yyyy hh:mm'" Any ideas would be very appreciated. Shawn -- sbvb ------------------------------------------------------------------------ sbvb's Profile: http://www.excelforum.com/member.php...o&userid=24604 View this thread: http://www.excelforum.com/showthread...hreadid=381924 -- sbvb ------------------------------------------------------------------------ sbvb's Profile: http://www.excelforum.com/member.php...o&userid=24604 View this thread: http://www.excelforum.com/showthread...hreadid=381924 |
| All times are GMT +1. The time now is 10:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com