1

I have a strange problem with Execl. The value of a specific cell ist 01.01.1900, the format of the cell is date-Format. When I try to count how many instances of 01.01.1900 there are via CountIf the result is 0, the search term is created via CDate()-conversion. Here's the complete code:

Dim searchterm as Date
Dim someRange as Range
searchTerm = CDate("01.01.1900")
Application.WorksheetFunction.CountIf(someRange, searchTerm)

The whole thing works as expected when the date starts before 01.03.1900. With the date in question 01.01.1900 I get the following results:

VarType(singleCellRange) returns 7 (which is date format)

singleCellRange.Value returns 31.12.1899 (see also here)

So the question is: how do I get the right format for the problematic dates in order to make CountIf work again?

Albin
  • 1,000
  • 1
  • 11
  • 33

2 Answers2

3

The VBA Date data type and Excel dates are not the same.

They are both stored as serial numbers, but with Excel, (using the 1900 system):

  • Excel, (using the 1900 system): 1 --> 1 Jan 1900
  • VBA: 1 --> 31 Dec 1899

Since Excel recognizes (incorrectly) 29 Feb 1900 as a valid date, the underlying values become congruent on 1 Mar 1900 and thereafter.

Depending on the importance of counting dates in this range, there are a variety of workarounds.

Probably the simplest is to just subtract 1 from the vba date comparator if the date is prior to 1-Mar-1900.

EDIT: The explanation for this bug, according to some, is said to be for compatibility with the premier worksheet at the time Excel was introduced: Lotus123, which had the same bug. So it has been around as long as Excel, and it is unlikely it will be changed, for fear of breaking programs that take it into account.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • There's a [Microsoft article](https://learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year) on this subject which mentions the Lotus 1-2-3 compatibility issue and why this bug in Excel won't be fixed – barrowc Jan 18 '20 at 23:32
  • 1
    @barrowc That article from 2019 echoes others that have been written for more than 20 years. But it is a good summary. – Ron Rosenfeld Jan 19 '20 at 01:54
  • interesting, so in order to read out a date in a cell correctly I always need to check if it's value is before 1.3.1990, and substract 1 otherwise I get the wrong date. Possibly add another check in case MS decides to fix the bug in the future. – Albin Jan 19 '20 at 13:13
0
Sub CountDate()
  Dim searchterm As Date, sh As Worksheet, someRange As Range
  Set sh = ActiveSheet ' use here your worksheet
  Set someRange = sh.Range("A2:A13")
  searchterm = CDate("01.01.1900")
  If CLng(searchterm) < 61 Then
      Debug.Print Application.WorksheetFunction.CountIf(someRange, CLng(searchterm) - 1)
  Else
      Debug.Print Application.WorksheetFunction.CountIf(someRange, CLng(searchterm))
  End If
End Sub

All the credit must go to @Ron Rosenfeld who explained why that...

Since both Excel and VBA start matching the same date only after first of March 1900 and the serial number for this date is 61, then conditioning the search in this way, you will always obtain the correct match.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27