OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: RE: [office-formula] serial number? Don't we mean serial date?

• From: robert_weir@us.ibm.com
• To: office-formula@lists.oasis-open.org
• Date: Wed, 27 Jan 2010 16:40:48 -0500

```"Dennis E. Hamilton" <dennis.hamilton@acm.org> wrote on 01/27/2010
03:36:57 PM:

>
> RE: [office-formula] serial number? Don't we mean serial date?
>
> Rob, I mainly agree that there is no "type," Serial Day, but there are
an
> incredible number of places where the current OpenFormula functions
produce
> and accept Number values that are interpreted as serial-day values as
part
> of the definition of those functions.
>

Can you give a few examples of where that is necessarily so? In other
words, where would this not be adequately covered by an implicit
conversion from date to number?

And remember, if we don't define that implicit conversion then we fail to
account for Excel's ability to take a square root of a date.

> I agree we should abstract this.  But I don't think we can avoid talking
> about the serial-day interpretation of Number type values.  There are
also
> some quality matters to consider, such as what the minimal fraction of a
day
> is that must be reliably represented and what the corresponding
limitation
> on the span of serial days becomes for adequately-approximatable
date-time
> and for exactly represented dates, respectively.
>

I don't think we need to say anything about this.  Date parameters take
date values.  Period.  If we don't state a precision requirement for =1/2
then why would we state it for date calculations?  (Of course, I'd be
happy to

>  - Dennis
>
> PS: I think epoch is an unfortunate choice, because it confuses the
> origin-date assigned to serial day 0 or serial day 1 in a mapping with
what
> year 0 or year 1 of a given calendar system represents in terms of
> historical events.  These strike me as independent.  (Geological epochs
we
> don't confuse with, one would hope.)
>

Again, I don't think we need to mention serial dates at all.  So that
problem goes away.  The epoch is N, where N is implementation defined and
declared.  I don't think we need to say more than that.

> THE USUAL ADDITIONAL THOUGHTS
>
> Many OpenFormula functions depend implicitly on the global
origin-date/epoch
> value and the calendar mapping that goes with that origin date.  This is
> true for anything involving a start-date and end-date parameter,
especially
> YEARFRAC and anything that has time-period basis adjustments that depend
on
> the actual calendar periods involved.  We can't define these functions
> without acknowledging the existence of a mapping known to the
implementation
> of the functions and talking about the dates those mappings correspond
to.
> The DATEDIF function is a particularly interesting case of this
> interdependence.
>

YEARFRAC takes date parameters, right?  So it is defined in terms of date
arithmetic, not in terms of serial numbers.

Remember, it should be possible to implement OpenForumula using a date
structure unrelated to date serial numbers, say a structure with year,
month, day, hour, minute and second fields.  Every single formula should
be calculable using such an implementation, without conversion to a serial
number.

Of course, I'm open to being wrong here.  But I'd like to see an example
where an OpenFormula function actually knowledge of the serial number
representation.

> The other wrinkle is if there are mappings to and from text strings as
part
> of OpenFormula itself.  If any of those mappings involve date formats
(not
> numbers), the issue shows up there too both for literals and string
values
> received from references (although we could have an implementation
finesse
> that case, just as for Logical/Number).  [I don't recall seeing anything
> that would make or take a string holding a calendar date, but I am
hedging
> my bet here.]
>

There is DATEVALUE().  In Excel that dates a string representing a date
and returns a number representing a date.  But I think can also be stated
as returning a Date, and then that Date can be implicitly converted to a
Number.

In the end it is similar to the Boolean/Number duality.  We don't require
a unique Boolean type.  But we define functions in terms of Booleans
wherever possible.  We probably want to do something similar with Dates. I
think it makes the definitions much cleaner as well as gives
implementations that wish to offer type checking (via warnings or
whatever) some guidance on which functions really take dates like
YEARFRAC() and which ones really take Numbers like SQRT().  This is an
important distinction, even though many current implementations blur it.

In other words, I think we need to define Boolean/Date/Number calculations
in such a way that there is no way a user can determine whether they are
implemented as three distinct types or whether they are all floats.

-Rob

>
>
> -----Original Message-----
> From: robert_weir@us.ibm.com [mailto:robert_weir@us.ibm.com]
> http://lists.oasis-open.org/archives/office-formula/201001/msg00098.html
> Sent: Wednesday, January 27, 2010 11:46
> To: office-formula@lists.oasis-open.org
> Subject: RE: [office-formula] serial number? Don't we mean serial date?
>
> This is really very simple.  We don't need to refer to serial dates at
> all.  There are dates and there are numbers.
>
> [ ... ]
>

```

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]