Dates As Internal Numbers
When you type a date into a cell, Excel stores it using an internal number. For example, 6/1/2001 is actually stored internally as 37043, which just so happens to be the number of days that have elapsed since 12/31/1899. Cell formatting determines how the value is displayed. You can verify this by typing a number into a cell and then formatting it as a date. For example, if you type 37043 into a cell and then click Format / Cells... and pick Date in the Category list, the value will be displayed as 6/2/2001.
Cell References and Internal Numbers
When you reference a cell in a formula, Excel provides the internal number instead of what you see displayed on the screen. For example, suppose you type 6/1/2001 into cell A1. The following formula:
="The internal value is " & A1
Will evaluate to:
The internal value is 37043
Using the TEXT Function
Unfortunately this fact produces undesirable results when you attempt to reference a cell containing a date in the filter argument of a workbook function. Microsoft's filter technology expects date values to be formatted with slashes and Microsoft's internal date format is incompatible with that. When you attempt to use the internal number as a date value, you are likely to get an error such as #ERROR? (-2147352571 - Type mismatch.). To work around the issue, you can use the TEXT function. The TEXT function will convert the internal number to the format you specify.
If you modify the formula above to use the TEXT function:
="The internal value is " & TEXT(A1, "MM/DD/YYYY")
it will evaluate to:
The internal value is 6/01/2001
Filter Example
The following formula is an example of how to sum the JC Transactions based on a cutoff date in cell A1:
=TSSum("CURRENT_JCT_RECORD_1","TAMOUNT","((TRANDAT <= '" & TEXT($A$1, "MM/DD/YYYY") & "'))")
The following formula demonstrates how to count the GL Transactions based on the accounting date in cell B5:
=TSCount("CURRENT_GLT_RECORD_1","((TACCTDT = '"& TEXT($B$5,"MM/DD/YYYY") & "'))")