First of all. Needing a condition like ISDATE (as oppsed to or subdividing ISTEXT and ISNUMBER) should be considered an indicator of bad sheet design. This aside for now.
In addition to what @Opaque said: If you abstain from setting horizontal cell alignment explicitly, Calc will always show you the type of any cell's content or formula result: The Default horizontal alignment will show Text left aligned and Number right aligned. Text looking like something better represented by Number is uncovered this way.
There is also the tool >View>Value Highlighting, shortcut Ctrl+F8, but I personally prefer the first way.
Beyond that: Calc cells only know two types for whatever cell content or result of a well working formula:
1 : Number
2 : Text
In specific there is no type like 'Date'.
Isn't there?
Hmmm. For some (bad?!) reasons Calc pretends to handle more types. That's a fake, but actually Calc tries to be smart and assigns number format attributes to cells based on its guesses (called "recognition" then) analyzing an input or the kind of a formula.
On the other hand there is no standard function telling you how the decision turned out. You need to look at the cell and do judge based on the format it probably got applied automatically.
Can't I have something looking at that as my agent?
In fact my statement about the exactly two types doesn't tell everyting. The NumberFormat of a cell isn't only represented by a code (and effectuated for the view) but also gets assigned a number ("key") used to point to it when using a respective service. There also is stored a Type property based on a different concept, and now there is a diversity of types includig DATE and CURRENCY and many more.
Your agent?
That's the crux. Afaik there are no standard tools.
Fortunately it's rather simple to create such an agent as an "introspective" function in Basíc (e.g.)
Unfortunately resorting to user code comes with disadvantages. ... This aside for now again.
Eventually you may try the following Basic module. (It uses VBAsupport because this is the simplest way to pass a CellRange as CellRange (as opposed to contained data) to a function. In a Calc formula you give the range without needing to know that it will be evaluated under VBAsupport.
REM ***** BASIC *****
REM Running only in LibO V 6.1 or higher due to the usage of cell.FormulaResultType2.
Option VBAsupport 1
Function numberFormatType(pVbaRg)
numberFormatType = ":fail:"
On Local Error Goto fail
rg = pVbaRg.CellRange
uR = rg.Rows.Count - 1
uC = rg.Columns.Count - 1
Dim out(uR, uC)
For r = 0 To uR
For c = 0 To uC
rc_cell = rg.getCellByPosition(c, r)
nft = ThisComponent.NumberFormats.getByKey(rc_Cell.NumberFormat).Type
rc_FormattedNumber = ((rc_Cell.FormulaResultType2=1) OR (rc_Cell.Type=1))
out(r, c) = IIf(rc_FormattedNumber, 1, -1) * nft
Next c
Next r
numberFormatType = out
fail:
End Sub
The output is an array if not only a single cell ... (more)
^[:digit:]{2}/[:digit:]{2}/[:digit:]{2}does check for text looking like a date, but does not check for a real calc date, which is a number (i.e. integer counting the days since 1899-12-30, which is day0[zero]). See also ODF Specification of DateIf you are using the whole column as reference, do not, sumproduct doesn't shortcut on the las row wiht data, like other functions does.
Dates actually being texts (as opposed to formatted but not converted numbers) are a dangerous mess with one exception: Given in ISO 8601-extended text format (
YYYY-MM-DD). Values in cells formatted as dates must also be assured to not have a fractional part.In specific the "slashed-three-times-two-digit" things are ambiguous in more than one terrible way.
Whatever you already know or learn here about dates and how to recognize them: Use it to get rid of that evil once and for all. A spreadsheet isn't a birthday card to aunt Mary, and it should contain data, not funny puzzles.
@mariosv:
That is a misconception. Those "other" (not forcing array context on their arguments) functions do not "shortcut" on the last data row, but for a cell range argument where a single scalar value is expected an automatic intersection with the formula cell's position is generated; for example, if the argument's range is A1:A6 and the fornula cell is positioned in row 4 then the intersection A4 is used. A formula cell position not within rows 1 to 6 generates an error.