Good morning.

Sorry for a possible rant, below, but all I want is really understand a
phenomenon which could not be explained to me, neither in the SoftMaker Office
Forum, nor in the Gnumeric mailing-list. That LibreOffice is affected by the
same, -apparently natural law-, does not help.

I will attach a screen shot below this post, which documents several things,
and especially one reality that I have trouble to cope with:

“No date is also a date”.

Not that it meant any specific date, as different spreadsheet programs chose
different days for the occasion (the 30th of december 1899 in case of
SoftMaker Office). Edit: Lotus 1-2-3 and those which followed.

If you are accustomed to this, I put my question to you right away:
“Why?” Why does a reference to an empty cell means a cell formated
as date with a reference to a date-value?

What I expect is an empty cell, where an empty cell is referenced. Instead it
goes like this:

1. Some table cells are individually formated to receive date-values
2. You enter dates into these cells
3. Somewhere in the table you choose to reference these dates
4. As the sequence of the references shall be the same as in the original
   cells, you enter only the very first reference, then just use the mouse to
   draw the lower right corner of the cell to wherever you want.
5. And as you do not care to where the sequence ends, you draw the mouse a
   little further than necessary ...
6. WOW! And you learn what your spreadsheet considers a reference to an empty cell.
7. From now on you invest effort and energy to avoid an error that the
   developers call *YOUR* error, as all they did was implement what
   Microsoft® Excel does the same way for decades.

For Gnumeric, the answer is that the core-language cannot be altered and so
no improvement is to be expected.
SoftMaker's argument is almost hard to reproduce as they content with saying
that Excel does it like that.

Imagine your inside rearview mirror were adjustable from outside... that would
be equally handy, no?

P.S.: None of the programs at my disposition reproduces the original date-format.
P.P.S.: I am developing a tiny script which removes 0-values from spreadsheets, in case that they are reproduced as the 30th of December 1899. It should be adaptable to other dates. Suppressing 0-values from spreadsheets does not work for date-cells... Make that my second question. Edit: Make that “selectively” suppressing 0-values. It does work globally for a whole sheet and all 0-values.

Attached Thumbnails

 

The date(and time) is just a number and what the authors reference as 0 is called the epoch. Since an empty cell is considered 0 you see 30 Dec. 1899.

https://en.wikipedia.org/wiki/Epoch_(reference_date)

You could put an if statement in that cell to display blank if zero or the value if not.
=IF(d1=0,"",d1)

Thank you for the effort. I have nothing against '0' being equivalent to the epoch. I am allowed to configure a cell as a date; but if my choice to NOT configure other cells alike is not taken into consideration afterwards, why are we programming anything in the first place?
I mean, who in the world says: “When I make a reference to an empty cell, all I want is that in the target cell something like the 30 Dec. 1899 will be displayed.”.
What they tell me is: “It has to be that way, live with it”.

Yes, I know. It is terrible, isn't it?

I am probably misunderstanding this but if I call a method which requires an integer with a null pointer it will give me problems.
I do feel your pain as Excel's quibbles have annoyed me for years. However, I think the issue here is that macros should be written and not recorded. I know Excel lends itself to macro recording and many people use it but if you actually read the VBA that they use it's far from optimal. My answer is to write the macro yourself in whichever language is available.

You think too complicated. This is not about automation, just ordinary cell-references: You enter into an arbitrary cell.., e.g.
The content in the cell will now be this reference (=D2) but the value at display will be the value from D2. If D2 is not formatted as a date, in my above scenario and screen shot, a date-value is displayed anyway and although D2 is empty! That is all that I lament for the time...

I fear that you are not thinking complicated enough.
Excel is full of odd conversions and strange types -- it's just generally not a good programing environment. That one can program it is, possibly, a good thing. But a spreadsheet is not the place for proper data analysis.

@273: I do not understand why you keep talking about programming Excel. I do not record macros, I do not write macros, I do not even have Excel and I do not care to “program Excel”

I want a cell-reference to an empty cell to be an empty cell or an explanation why it has to be different. Full Stop.

When I write a script to correct the behavior of the spreadsheet programs I know, I do this in Ruby and as a last resort.

OK. What's a data type? What is a pointer? What is an initialised versus an uninitialised pointer?

When you reference an empty cell, a default value of 0 is returned. If you do that in a cell that is formatted to display as a date, you are going to get "30 Dec. 1899" just as though the referenced cell had contained 0. Perhaps you could use "IF(ISBLANK(), ...)" to suppress the epoch date display in such cases. See https://exceljet.net/formula/if-cell-is-blank

Nuance: You get that, even if the referenced cell is NOT formatted to display a date!!
I understand all of that and have done it.
What I do not understand is: We write Spreadsheet-programs to make us get rid of a paper-crayon and the old Texas Instruments TI56 calculator. Then we let a user do many things and stuff and what not... and reference an empty cell so he does not have to empty the target cell himself. Then we create a problem, that we would not have without programmed systems: A magically generated 30 Dec. 1899 appears... Then we talk about solutions to this kind of problem, as if it were obvious.

Well. It is not.

... but ONLY if your referring cell IS - seems fairly logical to me, just format your referring cell as you like.

I am not finished, yet.

This procedure implies that transferring cell-formats and sequences like it is proposed by all spreadsheet programs and as I describe in my original scenario, above, is useless and has to be avoided for calendar-dates.

I agree completely, as it is just like that. We can terminate the thread, here, if you prefer. However I believe, that a programmer can address one problem at a time and does not have to alter the way that the world functions, nor the way that calendar-dates are derived from numerical values.., and still. It would render spreadsheets more useful.
And I believe, that Microsoft® Excel is not a natural constant.

Edit: One difference of Gnumeric against other spreadsheet programs is the possibility to suppress 0-values for an entire sheet and irrespective of the applied cell-formats. This securely eliminates the 30. december 1899, but also all other 0-values, be they important or not. The other programs which allow this option for selected cells, do not propose it for dates. Choose.

Edit II: SoftMaker Office proposes both functions, selectively suppressing 0-values which excludes dates, and globally for a whole sheet, which includes dates.

Like it or not when using a spreadsheet one is writing a program to process data.

People who like being exposed to their software's exigence (instead of the inverse) use databases right away and some write Web-Interfaces (for a reason). Spreadsheet programs are advertised very differently. And the Gnumeric-example shows that developers have the freedom to decide. I fear, they lack ears to hear.

Hell! I have been a developer. But it is true, that I put the emphasis on have been, these days and: I am sorry!
One of the bravest things that I have been involved with, for money, was replacing OJB by Hibernate. Noone noticed the difference. Not even me. The same system comprised a Web-Interface and my boss once got a spontaneous call (!) from a user in a big company who praised the ease of use that came with a recent release (we put a mark in the calendar just to increase the pertinence of my remark).

Allowing 0-dates to be optionally eliminated, by use of a persisted setting, does not appear to be an equally big event.

Edit and by the way: The SoftMaker-CEO (again) provided, -not a solution to the original problem-, but a hint to comfortably and selectively suppress 0-dates. You can define a user-defined date-format and terminate its definition with two semicolons, like in
LibreOffice may consent to this but Gnumeric definitely does.

I will hit the resolved-button, as I won enough clarity from our discussion. My description of a possible solution may not appeal to many and this thread thus cannot contribute to any improvement of the current situation. CLOFI.

Blog

Blank cells are a fact of life for people dealing with large data sets and I wouldn't want anybody unable to work out an "ISBLANK" type solution anywhere near any data important to me. Spreadsheets should be difficult to use to underline how important working with data is -- a knowledge of logic and mathematics is vital before even starting such a thing. Time and again I've seen spreadsheets with formulae and recorded macros by people with no idea what, exactly, is being done and that is what is truly worrying.
Just because a lot of complexity is hidden in spreadsheet programs does not mean that they should be expected to "just work" or that they should not be programmed with just as much care and attention as any other date manipulation.