Feature request- isdate() and istime()

[Ewen Pring]

One for Stuart really,

Built in functions isdate(value) and istime(value) would be nice,
returning boolean values. So they would return true if the input was a
date or a time as appropriate.

There are already similar functions such as iseven, islogical, isnumber,
isodd.

This would assist me as I've got a sheet which takes input as times and
then does calculations, but if a user does not enter in the correct format
(eg puts 0917 instead of 09:17 for a time) it would help to abandon
subsequent calculations (by nesting them in an if clause, perhaps) rather
than come up with unpredictable results.

Regards to all

--
Ewen Pring, St. Albans, Herts
using RISC OS 5
https://timebus.co.uk/riscos/

[Nick Prince]

Hi Guys

I feel sorry for Stuart having this thrown at him. The answer is that there are no dates and times in a spreadsheet, they are just numbers that have been formatted in a certain way. That's the reason you can subtract dates from each other and find the number of days between. The integer part is the number of days from a certain date - 01/01/1900 for Excel - and the decimal part is the time. When you format the cells as date, a date appears, and similarly for time.

Therefore. I would be surprised if an ISDATE or ISTIME can be developed.

Kind Regards

Nick Prince

--
You received this message because you are subscribed to the Google Groups "Colton Software Fireworkz" group.
To unsubscribe from this group and stop receiving emails from it, send an email to colton-software-fi...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/colton-software-fireworkz/35ad29b459.ewen%40ti1.timebusmail.co.uk.

[Alan Adams]

In message <CAACvJ68=HBSX6WtF-Azp6ErPp7gRC...@mail.gmail
.com>

Nick Prince <nick.pr...@gmail.com> wrote:

> Hi Guys

> I feel sorry for Stuart having this thrown at him. The answer is that there
> are no dates and times in a spreadsheet, they are just numbers that have
> been formatted in a certain way. That's the reason you can subtract dates
> from each other and find the number of days between. The integer part is
> the number of days from a certain date - 01/01/1900 for Excel - and the
> decimal part is the time. When you format the cells as date, a date
> appears, and similarly for time.

> Therefore. I would be surprised if an ISDATE or ISTIME can be developed.

> Kind Regards

> Nick Prince

Unless I'm getting very confused, I think the problem here lies in a
significant difference between Excel and Pipedream, i.e. in Excel you can
tell it that a cell should be formatted as a date or a time. Using that,
Excel could detect an invalid input. Pipedream however decides the format
on the fly (which I find confusing on occasion).

To solve that would require a method for a function to read the displayed
text, rather than the underlying data.

--
Alan Adams

[Ewen Pring]

Nick Prince <nick.pr...@gmail.com> sent on 6 Feb 2022:

> there
> are no dates and times in a spreadsheet, they are just numbers that have
> been formatted in a certain way.

If you try this- make a new Fireworkz spreadsheet, enter a number
like 0917 in A1, enter a time 09:17 in A2, enter a date eg 01.10.2022 in
A3. Save it. Open it in a text editor. You'll see the following relevant
lines in it;

{S:S;C;0;0;917}
{S:S;D;0;1;09:17:00;;Time}
{S:S;D;0;2;01.10.2022;;Date}

So, Fireworkz is actually storing in the file a time and a date formatted
and categorized as such, rather than (for example) some huge integer
representing the number of seconds or days since 1900 or similar.

[Stuart Swales]

On 07/02/2022 14:48, Ewen Pring wrote:
> Nick Prince <nick.pr...@gmail.com> sent on 6 Feb 2022:
>
>> there
>> are no dates and times in a spreadsheet, they are just numbers that have
>> been formatted in a certain way.
>
> If you try this- make a new Fireworkz spreadsheet, enter a number
> like 0917 in A1, enter a time 09:17 in A2, enter a date eg 01.10.2022 in
> A3. Save it. Open it in a text editor. You'll see the following relevant
> lines in it;
>
> {S:S;C;0;0;917}
> {S:S;D;0;1;09:17:00;;Time}
> {S:S;D;0;2;01.10.2022;;Date}
>
> So, Fireworkz is actually storing in the file a time and a date formatted
> and categorized as such, rather than (for example) some huge integer
> representing the number of seconds or days since 1900 or similar.
>

Hi all,

Fireworkz does NOT store date/times as floating point numbers, unlike
Excel and friends. As you've demonstrated, the external representation
is indeed textual (and locale-insensitive). Internally it has a two-part
date/time field, one part being days-since-some-datum* and the other
part being a number of seconds. Both parts have one value representing
NULL (absent), but only one part may be absent. There are no separate
date or time types, just that combined date/time.

You can extract an Excel-compatible** floating point serial number from
a date/time using the N() function (and its integer part using INT()),
so you might be able to simulate those functions currently (in 2.00 on)
using:

isdate(A1) -> ("date"=type(deref(A1))) & (0<>int(A1)) & (N(A1)=int(A1))
Is the item referenced by A1 a date/time
AND its number of days is non-zero
AND it has no fractions-of-a-day part?

istime(A1) -> ("date"=type(deref(A1))) & (0=int(A1))
Is the item referenced by A1 a date/time
AND its number of days is zero?

Stuart

* 1.1.0001

** Dates earlier than 01-Mar-1900 do not match as Microsoft Excel
incorrectly believes 1900 to be a leap year.

--
Stuart Swales

[Ewen Pring]

Stuart Swales <stuart.swale...@gmail.com> sent on 7 Feb 2022:

> you might be able to simulate those functions currently (in 2.00 on)
> using:

Thank you very much for your explanation and these functions which are
very clear and work

> isdate(A1) -> ("date"=type(deref(A1))) & (0<>int(A1)) & (N(A1)=int(A1))
> Is the item referenced by A1 a date/time
> AND its number of days is non-zero
> AND it has no fractions-of-a-day part?

> istime(A1) -> ("date"=type(deref(A1))) & (0=int(A1))
> Is the item referenced by A1 a date/time
> AND its number of days is zero?

I'm thinking maybe also have another AND clause on the latter ...

& (N(A1)<1)

as otherwise a time outside the 24 hour clock would still count as a time.
Admittedly you might still say for example 26:12 is a time, or perhaps
more accurately a duration or a period of 26h 12min. Perhaps with the
extra clause the pseudo-function would be better called "istimeofday()"
rather than "istime()".