[Solved] Excel =ISDATE() equivalent

[svanslyck]

Hello Open Office Expert Gurus.

Can someone please tell me...

... 1. what is the OpenOffice Calc equivalent of Excel's ISDATE() function?

... 2. how to answer such questions for myself in the future?

Thanks.

[John_Ha]

See Calc Functions

[karolus]

Hallo
There is no equivalent for ISDATE neither in AOO nor LO.

[svanslyck]

Thanks. Looks like the closest is ISNUMBER().

[Lupp]

How would the questioner explain without a reference to the mentioned Excel function what he (f/m) would accept as a "date". Is the question about strings "recognizeable" as dates or not? is it about cell contents or results and probably the formatting of resulting numbers?

Neither Excel nor Calc have an actual data type "DATE". I haven't access to any Excel, but the web is filled with lots of stuff about it. My terse research revealed that the mentioned function can be used in some versions of Excel as well in sheet formulas as in VBA code, but in others only in VBA code. Anyway it is kind of nonsense only testing if a given string is a "valid date". What a "valid date" is supposed to be must then be left to the locale settings. Since the treatment of dates is a nightmare anyway, even Calc can't do much better.
However ISNUMBER() is not usable as a surrogate because it will answer NO for any string passed to it without further checks. You may try the expression

Code: Select all

ISNUMBNER(DATEVALUE(myString))

to get something similar to the supposed ISDATE() of Excel. The problems concerning deprecated textual representations of dates will also be present in Calc then, of course.
Of much more value would be a user function testing a cell for whether it holds a date or not based on the NumberFormat the cell has assigned. NumberFormats (not the keys, but the objects) have a property .Type supporting the distinction (depending on some circumstances and with variants). An introspective UDF for the purpose isn't difficult to write.

[svanslyck]

[OP deleted]

[Villeroy]

I have used excel since it first came out and I use the ISDATE() function repeatedly.

https://a4accounting.com.au/excel-isdate-function/

Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.

Spreadsheets do not have any dates as a separate data type. All spreadsheet dates are formatted numbers. If Excel had ISDATE it would return whether a cell has a number that is formatted as date or not.

[Zizi64]

Supported format codes from the Help of the CELL()/FORMAT function:

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

Unfortunately some format codes are recognised as Dx, some others are recognized as G (depends on the locale of the format code or on the manual/style formatting?)...
LibreOffice 6.1.6

[keme]

I have used excel since it first came out and I use the ISDATE() function repeatedly.

It may be so. Nobody here finds it (and there are several experienced Excel users around here, I assure you), so it is not likely to be a builtin spreadsheet function.

I cannot claim 37 years experience with Excel, but I can read documentation. Microsoft's documentation is not my favorite, but from what I gather thence:

• Spreadsheet: Search reveals no ISDATE() spreadsheet function in Excel.
  so if you have that, it seems to be an addon that you may have made yourself or gotten from others.
• Database: There IS the IsDate() SQL function, which takes a string and determines whether it can be interpreted as a date.
  This can be emulated by combining spreadsheet functions.
• Macro code: There IS the VBA IsDate() function, which takes any argument and determines whether the data type is Date
  Most likely to be useful with Variant type data, I guess. If this is what you need, other helpers know StarBasic type handling better than I do. Just comment below to the effect that "this is what I was after".

The SQL function can be easily emulated in spreadsheet formulas, if you need to validate textual data.
If you can use reversed logic, this returns TRUE when the data is NOT possible to read as a date:

Code: Select all

=ISERROR(DATEVALUE(<data>))

If you must have the logic as ISDATE specifically, use NOT() to reverse the formula.

Code: Select all

=NOT(ISERROR(DATEVALUE(<data>)))

Note: The above will give different result in Calc and Excel for some dates, for a couple of reasons:

• Excel does not handle dates before the year 1900, so dates before that will be valid in Calc but not in Excel.
• Excel assumes 1900 was a leap year, so 29th. of february 1900 is considered a valid date in Excel but not in Calc

Also, Calc assumes the earliest possible transition from Julian to Gregorian calendar, which renders invalid all dates from 5th to 14th of October 1582. Many parts of the world made the transition at a later date, in which case those "invalid" dates still make sense.

[Lupp]

I have no record about every URL I visited before I made my statements. One of them still available in one of my browser tabs was https://www.techonthenet.com/excel/formulas/isdate.php .
There we find a typical example of such content pretending to be information, but actually being disinformation - at least to my brain.
They say that ISDATE "returns TRUE if the expression is a valid date." They do not say what a "valid date" is supposed to be. All the given examples are nonsense, imo. All values taking the expression position are strings (ISTEXT()=True) though the expression was introduced as Variant. One is "1/3/2004" without referring in any way to the locale dependency, a second one is "January 3, 2004" Aha. The only thing I can learn from such nonsense is that the author most likely writes in the USA.

Well, that's not an official text by MS, but it is the kind of "information" I next to always get if I try to reserach about the ways MS-Office works. A rare miracle of purity: Pure Time Wasting.

The fact that none of the examples would say "yes" when asked =ISNUMBER(expression) with an ordinary numbr in the expression position, but the OQer announced the usage of that formula as a surrogate for the missing =ISDATE(expression) was the reason for me to step in. I didn't expect my answer to be final, but mainly hoped to get clearer information concerning the question:
What is a "valid date" under this/that/another CONDITION?

Every vendor of software, help-text writer, poster of a related question should know that this must be the central concern.
To provide a standard function pretending to decide a question not giving a sufficiently clear specification of the correct answer under any expectable conditions should be seen as a criminal offense.

[John_Ha]

... 1. what is the OpenOffice Calc equivalent of Excel's ISDATE() function?

As ISDATE() is not an Excel function, it would help greatly if you could explain exactly what your ISDATE() function does. When we know exactly what you expect it to do then we may be able to suggest a way of doing it.

It would also help if you could upload a spreadsheet file (not an image) showing a number of examples where your function gives each of the various answers.

[svanslyck]

All right, my mistake folks. I was working from home. There is no ISDATE() function within Excel.