At 18:52 02/05/2013 -0700, V. S. Harris wrote:
I have a spreadsheet with three columns: year, month, day. In the
4th column I use the DATE() function to create a date from these
columns. It works fine if there are valid data, but if the month and
day column are empty, the results are surprising.
For example:
DATE(1995,blank,blank)
is the same as:
DATE(1995,0,0)
which turns out to be:
30 Nov, 1994
You seem to be equating empty with blank, which are not the same, but
the results in this case are apparently the same for both.
I suppose this result is not particularly surprising. If month one
of 1995 is January, then month zero is December 1994; the zeroth of
December will be the day before the first, so 30 November.
I would rather the system generate an error warning (e.g., data out
of range) than silently produce a counter-intuitive result.
Am I missing something obvious here?
I think all you are missing is that in situations such as this it
becomes your responsibility in designing the spreadsheet to cope with
such problems. If your first three columns of data are entered
manually, you can arrange to restrict the values entered - but it may
be difficult to cope with days that are generally valid but become
invalid when interpreted in combinations with a month value. 31 is
a valid day, of course, but not in combination with a month value of
2, say. But in any case, it would be silly to enter dates manually
as three separate values like this; instead you should take advantage
of the automatic date recognition of a spreadsheet and enter dates as
a single value in a single cell. If you need the separate parts, you
can derive these from the single date value.
If - as is more likely - you are developing the values in the three
columns from other data, then you should take care to trap
exceptional cases, so that you do not hand empty, blank, or zero
cells as parameters to the DATE() function.
Another possibility is that your data is created from another source,
perhaps in the form of a CSV file or similar. In that case, it is
again your responsibility to build checks into the way you use the
data. You can use the IF() function to detect inappropriate data and
to create the error messages you desire.
I trust this helps.
Brian Barker
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
