Set foundRange = Rows(1).Find(someDates(cnt)) SomeDates(9) = 5 - IIf(bln1904, offsetDays, 0) ' (in VBA)
The code below is a modification of yours, to find out about all the options there: Public Sub TestMe()ĭim bln1904 As Boolean, offsetDays As Integer, blnOffset As Booleanīln1904 = (vbYes = MsgBox("Switch temporarily to Date1904?", vbYesNo, "Date Mode"))īlnOffset = (vbYes = MsgBox("Apply Offset Days for 1904 to date serials? (1642 days)", vbYesNo, "Date Conversion"))Ĭells(1, 1) = CDate("01-01-1902") ' will fail to write the date only in Date1904Ĭells(1, 2) = CDate(-200) ' will fail to write the dateĬells(1, 3) = CDate("31-03-2012") ' in Date1904 will write 30-03-2008Ĭells(1, 4) = CDate("31-10-2012") ' in Date1904 will write 30-10-2008 Even though, the comparisons using Long or Double (serial date) do not seem to work if you do not apply the offset of days. I don't think you can work it around at all, unless you avoid to mix source data using different systems. Not sure if it's a release cut, but I am pretty sure they had waited the chance to get rid of further support in this sense. However, when you go to do operations on the Worksheet, limitations apply. I guess that what is on the VBA as Date preserves its identity, regardless of the Date System used in that specific Worksheet.
So, yes, there is some kind of conversion behind to refer to 31-12-1903 otherwise, for 1904 System, it would have tried to find ( 14 System => which is 2923 for the 1900 System = 1461 + 1462), and the error would have not showed up (1904 serial > 0). As the Worksheet is switched to the 1904 System, that date is not supported. It means that when calling Range.Find, it does interpret 1461 as 31-12-1903, so as per 1900 System. If Not foundRange Is Nothing Then = vbGreen MsgBox strMsg, vbInformation, "Gap found" StrMsg = "Range.Find failed for CDate(" & cnt & ")" & Chr(13) & _ Set foundRange = Rows(1).Find(CDate(cnt)) The following code gives you the first serial date that fails in 1904 Date System -> 1461 (VBA / 1900 Date System: ): Public Sub TestMe2() So, it makes sense that you fail to find something that should not be able to write in the Worksheet. Perhaps it never should have worked? if you try the following code, it will fail to write on the Worksheet environment (Office 2016): Cells(1, 1) = CDate("01-01-1902") ' will fail to write the date only in Date1904Ĭells(1, 2) = CDate(-200) ' will fail to write the date (runtime error 1004) So, it seems that in Excel 2016/2013 under the Date1904 rule, there is some kind of a check that is made that is before the year 1904 and thus it cannot be parsed to a date in the Excel date system? While in Excel 2010 this is not the case In the 1904 date system, the first day that is supported is January 1, 1904 it does say that the 1904 Date System does not support dates before 1904 here. Although does not specify that it will fail.