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 @anon73440385 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’.
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.
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
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
numberFormatType = out
The output is an array if not only a single cell was passed. It will give per cell the NumberFormat type as a number. The explanation (2 for DATE e.g.) you find here:
LibreOffice: com::sun::star::util::NumberFormat Constant Group Reference.
Of course you can use this output with COUNTIFS() or whatever function expecting an array on the respective parameter position.
If bit 1 is set for
NumberFormat.Type, the format isn’t a predefined one, but created by the user.
A user defined format recognized as a date format therefore gives the result 3 (2 + 1 e.g.)
If the assigned number format currently not is applicable, a negative sign should be applied.