IsDate function not directly available in LO calc 7.1.2.2 (EN)?

[Bruce_S]

I believe I am having a problem with the IsDate function.

This function works in a google doc cell, and is documented in the LO help for 7.2 ( IsDate Function ), but doesn’t appear in the list of logical functions in LO calc.

Is this function only available in BASIC subroutines in LO calc?

If so, this seems a shame – it seems a natural logical function to have in the toolkit.

(Context: I have a largish spreadsheet of names and dates of birth. Sometimes, only the year of birth is known, and entered as a numeric value YYYY. I wish to extract (into another column) only the year of birth, for which the YEAR() function is handy if the data is a full date value. However, YEAR() fails on the four character number (when only the year is known). Hence, ISDATE() would be helpful in this context).

[mariosv]

Using function CELL(“format”;address), could help, as usually dates in cells have a date format.
LEFT(CELL"format";address))=“D”
.
Returns a character string that indicates the number format.
, = number with thousands separator
F = number without thousands separator
C = currency format
S = exponential representation, for example, 1.234+E56
P = percentage
In the above formats, the number of decimal places after the decimal separator is given as a number. Example: the number format #,##0.0 returns ,1 and the number format 00.000% returns P3
D1 = MMM-D-YY, MM-D-YY and similar formats
D2 = DD-MM
D3 = MM-YY
D4 = DD-MM-YYYY HH:MM:SS
D5 = MM-DD
D6 = HH:MM:SS AM/PM
D7 = HH:MM AM/PM
D8 = HH:MM:SS
D9 = HH:MM
G = All other formats

• (Minus) at the end = negative numbers are formatted in color
  () (brackets) at the end = there is an opening bracket in the format code

https://help.libreoffice.org/latest/en-US/text/scalc/01/04060104.html?DbPAR=CALC#bm_id3155509

[Bruce_S]

Thanks, I’ll give this a try. It’s a good idea, that I wouldn’t have thought of.

[mikekaganski]

@Bruce_S

Is this function only available in BASIC

Yes, and the help page you mentioned is BASIC help, not Calc’s.

@Bruce_S

it seems a natural logical function to have in the toolkit

It is not useful in Calc at all. In Calc, there is no date data type. Any “date” is actually a simple number, and only cell format can show you a number 45027 as 2023-04-11 (only if the null date is set in settings to usual 1899-12-30; the same number can show you as 2023-04-13, or 2027-04-12, when other null date values are selected).

There is no way for a function to distinguish between numbers and dates in Calc. So only the ISNUMBER is provided.

Note that YEAR does not “fail” on 4-digit numbers - it only results in an unexpected result. Which is because YEAR also can’t know if this is a date or not - if it could, it would give you an error instead on non-date input.

[Bruce_S]

Fair enough. I wouldn’t go so far as to say an IsDate function would not be useful at all, in Calc. I would agree that adding it would be a heavy lift. In my case, sometimes the year only has been added as character data (strings YYYY) or as numeric data (NNNN). I have found that Year(NNNN) returns 1905 in my current calc configuration (for NNNN=2023), which is an unwelcome surprise, not easily trapped. But again, I wouldn’t lobby to modify Calc to support an IsDate function, if it requires defining a date data type (yikes).

[mikekaganski]

@Bruce_S

I would agree that adding it would be a heavy lift

No, it would be a breaking change to infinite number of spreadsheets that use ISNUMBER for dates, and otherwise rely on the specific implementation of Calc that does not have a dedicated Date type. So while I don’t (and didn’t) say that the function has no use generally, it definitely is not useful in the specific program, which has its architectural decisions made years ago, and changing which is basically impossible.

[Villeroy]

Spreadsheets (Excel, Calc and similar) do not know any date values. A date is either a character string when wrongly imported or wrongly typed in or it is an integer day number starting with 1899-12-30 (day zero).
Leap_Year_Bug.ods (30.3 KB)
The attached spreadsheet was made to demonstrate Excel’s leap year bug and why 1899-12-30 is Calc’s start of the epoch.