LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 10-06-2016, 03:39 AM   #1
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Excel-Imposed dumbness in every spreadsheet program


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
Click image for larger version

Name:	sc_gnumeric_no_date.png
Views:	42
Size:	16.7 KB
ID:	23180  

Last edited by Michael Uplawski; 10-08-2016 at 08:10 AM.
 
Old 10-06-2016, 07:15 AM   #2
michaelk
Moderator
 
Registered: Aug 2002
Posts: 21,946

Rep: Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314Reputation: 4314
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)
 
Old 10-06-2016, 01:21 PM   #3
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by michaelk View Post
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.
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”.

Quote:
You could put an if statement in that cell to display blank if zero or the value if not.
=IF(d1=0,"",d1)
Yes, I know. It is terrible, isn't it?

Last edited by Michael Uplawski; 10-06-2016 at 02:29 PM.
 
Old 10-06-2016, 01:31 PM   #4
273
LQ Addict
 
Registered: Dec 2011
Location: UK
Distribution: Debian Sid AMD64, Raspbian Wheezy, various VMs
Posts: 7,585

Rep: Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351
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.

Last edited by 273; 10-06-2016 at 01:33 PM.
 
Old 10-06-2016, 02:27 PM   #5
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by 273 View Post
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.
You think too complicated. This is not about automation, just ordinary cell-references: You enter into an arbitrary cell.., e.g.
Code:
=D2
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...

Last edited by Michael Uplawski; 10-06-2016 at 02:28 PM.
 
Old 10-06-2016, 02:45 PM   #6
273
LQ Addict
 
Registered: Dec 2011
Location: UK
Distribution: Debian Sid AMD64, Raspbian Wheezy, various VMs
Posts: 7,585

Rep: Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351
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.
 
Old 10-06-2016, 04:31 PM   #7
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
@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.
 
Old 10-06-2016, 04:37 PM   #8
273
LQ Addict
 
Registered: Dec 2011
Location: UK
Distribution: Debian Sid AMD64, Raspbian Wheezy, various VMs
Posts: 7,585

Rep: Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351
Quote:
Originally Posted by Michael Uplawski View Post
@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?

Last edited by 273; 10-06-2016 at 04:38 PM.
 
Old 10-06-2016, 09:04 PM   #9
rknichols
Senior Member
 
Registered: Aug 2009
Distribution: CentOS
Posts: 4,568

Rep: Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093Reputation: 2093
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
 
Old 10-07-2016, 02:55 AM   #10
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by rknichols View Post
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.
Nuance: You get that, even if the referenced cell is NOT formatted to display a date!!
Quote:
Perhaps you could use "IF(ISBLANK(), ...)" to suppress the epoch date display in such cases. See https://exceljet.net/formula/if-cell-is-blank
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.
 
Old 10-07-2016, 03:49 AM   #11
descendant_command
Senior Member
 
Registered: Mar 2012
Posts: 1,805

Rep: Reputation: 622Reputation: 622Reputation: 622Reputation: 622Reputation: 622Reputation: 622
Quote:
Originally Posted by Michael Uplawski View Post
Nuance: You get that, even if the referenced cell is NOT formatted to display a date!!
... but ONLY if your referring cell IS - seems fairly logical to me, just format your referring cell as you like.
 
Old 10-07-2016, 01:18 PM   #12
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by descendant_command View Post
... 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.

Last edited by Michael Uplawski; 10-08-2016 at 08:11 AM.
 
Old 10-08-2016, 01:00 AM   #13
273
LQ Addict
 
Registered: Dec 2011
Location: UK
Distribution: Debian Sid AMD64, Raspbian Wheezy, various VMs
Posts: 7,585

Rep: Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351
Like it or not when using a spreadsheet one is writing a program to process data.
 
Old 10-08-2016, 01:48 AM   #14
Michael Uplawski
Senior Member
 
Registered: Dec 2015
Location: Apples
Distribution: Apple-selling shops, markets and direct marketing
Posts: 1,211

Original Poster
Blog Entries: 32

Rep: Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670Reputation: 670
Quote:
Originally Posted by 273 View Post
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
Code:
DD/MM/YY;;
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

Last edited by Michael Uplawski; 10-08-2016 at 07:59 AM. Reason: CLOFI, Blog
 
Old 10-08-2016, 08:09 AM   #15
273
LQ Addict
 
Registered: Dec 2011
Location: UK
Distribution: Debian Sid AMD64, Raspbian Wheezy, various VMs
Posts: 7,585

Rep: Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351Reputation: 2351
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: 4 Spreadsheet Alternatives to MS Excel LXer Syndicated Linux News 0 04-05-2014 05:42 PM
Emailing LibreOffice Spreadsheet As Excel roger2143 Linux - Newbie 3 09-05-2012 07:30 PM
Saving in excel spreadsheet format vladimir1986 Linux - Desktop 5 12-13-2011 02:28 PM
Save data in excel spreadsheet? Shioni Programming 3 07-29-2006 08:15 PM
mySQL and excel spreadsheet zuessh Linux - Software 1 06-12-2003 07:26 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 12:31 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration