Re: [libreoffice-users] Odd behavior using DATE() function in calc

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+help@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