Ask Your Question

Criteria to check if cell value is a date

asked 2020-04-12 06:33:54 +0100

zf gravatar image

I am using a function such as COUNTIFS which requires a criteria. It seems you can't use e.g. ISNUMBER(A1:A6) as the criteria in order to check if a value is a date as opposed to text. Is the best approach to enable regular expressions for Calc and to use a criteria such as "^[:digit:]{2}/[:digit:]{2}/[:digit:]{2}" for date in the format of "MM/DD/YY", despite caveats such as the need to escape certain characters for all formulas and the fact that this seems to be a program-specific setting, potentially messing with documents from other users that use * as wildcards (the default setting)?

If it's the case that most experienced users would probably want regular expressions enabled, then it's not as big of a deal to make the switch to enable regular expressions and learn to deal with it.

I can use SUMPRODUCT as an alternative to COUNTIFS where I can specify the "criteria" using e.g. ISNUMBER(A1:A6), but for some reason it causes a performance issue for my spreadsheet.

edit retag flag offensive close merge delete


^[: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 day 0 [zero]). See also ODF Specification of Date

Opaque gravatar imageOpaque ( 2020-04-12 10:37:01 +0100 )edit

If you are using the whole column as reference, do not, sumproduct doesn't shortcut on the las row wiht data, like other functions does.

m.a.riosv gravatar imagem.a.riosv ( 2020-04-12 12:02:31 +0100 )edit

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.

Lupp gravatar imageLupp ( 2020-04-12 15:14:17 +0100 )edit


doesn't shortcut on the las row wiht data, like other functions

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.

erAck gravatar imageerAck ( 2020-04-12 22:17:58 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2020-04-12 11:00:23 +0100

Opaque gravatar image

updated 2020-04-12 11:56:24 +0100


if you want to check, whether a date is a real calc date, you just need to check whether it is a number (see also the the link provided in my comment). Hence

=ISNUMBER(A1) will check whether call A1 contains a date. If you want to check more precisely for integers (this way excluding date+time values) you may want to use: =IF(ISNUMBER(A1),INT(A1)=A1)

See also the following sample file: C:\fakepath\DateCheck.ods

Hope that helps.

If the answer helped to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more

answered 2020-04-12 22:06:49 +0100

erAck gravatar image

You can check if a cell's content is numeric and formatted as date:

edit flag offensive delete link more


@erAck If I open @Opaque's example attachment from his comment and use your function, LEFT(CELL("format";A2) returns G despite the cell already in date format (clicking on cell shows the date format button already toggled on). Toggling it off and on will then correctly return D--I'm not sure what the problem is.

Also, how would I use ISNUMBER as a criteria for COUNTIFS in this regard? I assume there's a distinction between "criteria" and "condition" for when describing the functions COUNTIFS and SUMPRODUCT, respectively, so I'm curious if it's even possible to use COUNTIFS for my situation (which I prefer for performance reasons).

zf gravatar imagezf ( 2020-04-14 03:23:41 +0100 )edit

I assume there's a distinction between "criteria" and "condition" for when ...

If you are interested in that, please read the long list of comments in the answer of @Mike Kaganski to this question:

Opaque gravatar imageOpaque ( 2020-04-14 10:35:09 +0100 )edit

returns G despite the cell already in date format

Sigh.. that seems to be due to the definition of the CELL() function taken over from Excel, see CELL function help (scroll down to FORMAT). Sorry, didn't think of that.

IMHO the ISO 8601 date format (and any other with all day, month and year codes) should be included with D1 (for the "and similar formats" description). Might be worth a bug report. Done with tdf#132106.

erAck gravatar imageerAck ( 2020-04-14 21:40:13 +0100 )edit

answered 2020-04-12 13:39:28 +0100

Lupp gravatar image

updated 2020-04-12 14:01:26 +0100

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
End Sub

The output is an array if not only a single cell ... (more)

edit flag offensive delete link more


Concerning the "Afaik" in my above answer I should probably note that I know the CELL() function and the "format" variant of it. However, I don't consider it something anybody should rely on.

Lupp gravatar imageLupp ( 2020-04-12 14:28:48 +0100 )edit
m.a.riosv gravatar imagem.a.riosv ( 2020-04-12 19:26:54 +0100 )edit

If you use VBASupport 1, you can try this way:

msgbox typename(Range("A1").value)

The return value may be "String", "Double", "Date", "Boolean", ...

sokol92 gravatar imagesokol92 ( 2020-04-13 19:25:02 +0100 )edit
Login/Signup to Answer

Question Tools



Asked: 2020-04-12 06:33:54 +0100

Seen: 653 times

Last updated: Apr 12 '20