Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

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:
is the same as:
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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.