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


Help: OASIS Mailing Lists Help | MarkMail Help

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?

I agree one can do automatic conversions, and that is the expected behavior.
It's the implicit/automatic conversion from number to date that is tricky.
The question is how is the conversion mapping to be known, since users can
manipulate numbers to accomplish determinations that are dependent on what
the automatic mapping will be.  (They also may just use serial-day numbers
to express time intervals, something I find myself doing a great deal in

 - Dennis


Clearly, the [Gregorian-understood?) DAY(year, month, day) function is an
explicit invocation of the applicable automatic function.  The question is,
how do we specify what that function depends upon.  How do we define the
various inverse functions (YEAR, MONTH, and DAY) in a consistent and clear

If we have the date (and know what the calendar is, of course), and know the
origin date, the serial day is derivable by an automatic conversion, and if
the function involved needs to recover the calendar date we can expect it to
do so using the appropriate "inverse" conversion.

However, for these functions that accept serial-day values (and are defined
to have those parameters), the inversion back to a calendar date is
required.  And for interoperability, there must be some way to assure that
the same global parameters are used by the producer and the consumer, either
because the OpenFormula-hosting specification fixes what that is or the
specification-conforming implementations have some way to signal the
appropriate parameters in a document instance, as OOXML does.

I think the mapping that Gnumeric does should be supportable.  I think
isomorphic mappings that have no 2000-02-29 should work and a variety of
origin-dates should work independent of the mapping choice.  I also think we
will run into requirements to support other calendar systems, or at least
OpenFormula-hostings might, and we should be tolerant of that.

Defining the parameters that need to be fixed by OpenFormula-hosting
specifications, including provision for them to be specified in the context
or be implementation-defined, seems to me to be much better than the
completely vague way we have it right now.  It seems clear that we cannot
freeze those parameters in OpenFormula and accommodate the various approach
out there and that must be supported, not least of all for legacy import and
export.  I think indicating what dependencies must be handled in the context
of an evaluation, and what the variability of those might be, is important.

 - Dennis 

-----Original Message-----
From: robert_weir@us.ibm.com [mailto:robert_weir@us.ibm.com] 
Sent: Wednesday, January 27, 2010 13:41
To: office-formula@lists.oasis-open.org
Subject: RE: [office-formula] serial number? Don't we mean serial date?

"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 
> incredible number of places where the current OpenFormula functions 
> and accept Number values that are interpreted as serial-day values as 
> 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 
> some quality matters to consider, such as what the minimal fraction of a 
> is that must be reliably represented and what the corresponding 
> on the span of serial days becomes for adequately-approximatable 
> 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 
> year 0 or year 1 of a given calendar system represents in terms of
> historical events.  These strike me as independent.  (Geological epochs 
> 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.

> Many OpenFormula functions depend implicitly on the global 
> value and the calendar mapping that goes with that origin date.  This is
> true for anything involving a start-date and end-date parameter, 
> YEARFRAC and anything that has time-period basis adjustments that depend 
> the actual calendar periods involved.  We can't define these functions
> without acknowledging the existence of a mapping known to the 
> of the functions and talking about the dates those mappings correspond 
> 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 

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 

> The other wrinkle is if there are mappings to and from text strings as 
> of OpenFormula itself.  If any of those mappings involve date formats 
> numbers), the issue shows up there too both for literals and string 
> received from references (although we could have an implementation 
> 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 
> 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 

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.


> -----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. 
> [ ... ]

To unsubscribe from this mail list, you must leave the OASIS TC that
generates this mail.  Follow this link to all your TCs in OASIS at:

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