Page 1 of 1

[Solved] Using form in Calc changes the date in date cells

Posted: Thu Apr 14, 2022 9:49 pm
by BörjeH
Problem:
I use Calc to process a lot of data posts and also do some calculations. Each post is a line defined with a date. I use the standard form like: 2022-02-14.

If I now now use the "Form" available under "Data" to add some more values in a simple way for each post the date will be changed to "1899-12-30". Why?

This is really annoying. I think the problem is that the cell format in a "Form" differs from my cell format used in my calc file.
But how can I fix this? Is it possible to change the cell format in the standard "Form"? Is there a simple guide available how to create a working "Form" with the same cell format? Or how should I solve this problem?

Or is this a bug in Office when using "Form" to be solved any time soooooon....?

Re: Using form in Calc changes the date in date cells.

Posted: Thu Apr 14, 2022 11:11 pm
by FJCC
I am not sure what you mean by the "Form available under Data". Can you upload a small file showing the problem? To upload a file, click PostReply and look for the Upload Attachment tab just under the box where you type a response.
1899-12-10 is the day whose value is zero. 1899-12-31 has a value of 1, and so on. If you are setting a cell formatted as a date to a value of zero, you get 1899-12-30.

Re: Using form in Calc changes the date in date cells.

Posted: Fri Apr 15, 2022 12:56 am
by Lupp
This seems to be a question concerning more recent LibreOffice Calc. I dont know a menu item like

Code: Select all

>Data>Forms...
in AOO.
LibreOffice Calc has such a tool, but it works as I would expect it in V 7.3.1.3. (Actually I never used it till today.)

Re: Using form in Calc changes the date in date cells.

Posted: Fri Apr 15, 2022 12:49 pm
by BörjeH
FJCC wrote:I am not sure what you mean by the "Form available under Data". Can you upload a small file showing the problem? To upload a file, click PostReply and look for the Upload Attachment tab just under the box where you type a response.
1899-12-10 is the day whose value is zero. 1899-12-31 has a value of 1, and so on. If you are setting a cell formatted as a date to a value of zero, you get 1899-12-30.
OK. below are some files.

I really like the idea with a form. It is then easy to input data in Calc. But I need the date in Calc, and I do not like that the form command changes the date if I change something in other columns for that line in Calc.

Enclosed is a simpel test file.

I also enclose a screen-shoot of where I find the form command. I also enclose a screen-shot of the actual form and the test file in operation.

Re: Using form in Calc changes the date in date cells.

Posted: Fri Apr 15, 2022 12:51 pm
by BörjeH
And a screen-shoot

Re: Using form in Calc changes the date in date cells.

Posted: Fri Apr 15, 2022 12:55 pm
by BörjeH
And the final screen-shot of the form in operation...

Re: Using form in Calc changes the date in date cells.

Posted: Fri Apr 15, 2022 1:21 pm
by Villeroy
That input form is crap. If you don't want to edit spreasdheet cells in place, you can use the Base component with real tables, records, fields and fully functional input forms.

Re: Using form in Calc changes the date in date cells.

Posted: Fri Apr 15, 2022 1:30 pm
by cwolan
Do you have the "DataForm" extension installed?
https://extensions.openoffice.org/en/project/dataform

Re: Using form in Calc changes the date in date cells.

Posted: Fri Apr 15, 2022 4:12 pm
by BörjeH
cwolan wrote:Do you have the "DataForm" extension installed?
https://extensions.openoffice.org/en/project/dataform
Yes, I have. Actually I had forgotten that I had installed it. :oops:

I now removed the extension and then installed it again. Now it worked with my test example! It also works with my larger project.

But the date representation in the "Form" pop-up window is not the same as in the calc file. It is month/day/year(4) in the form. I use year(4)-month-day in the calc-file.

There is also a funny error. If you start "Form" with a calc-file with the wrong altered dates it will stuck and crasch. If you correct the dates it will start OK.

It think the problem is now solved. It was the extension "Form" which gave these errors. I hope the developer of this useful extension tries to solve these errors.

Thank you Cwolan! :D

Re: [Solved] Using form in Calc changes the date in date cel

Posted: Fri Apr 15, 2022 4:22 pm
by Villeroy
That lousy extension would be pointless even if it would be completely rewritten.

Download and extract https://download1076.mediafire.com/7u6w ... /dBase.zip
Open dBase.odb. Open the form named "Data". This most simple demo of an input form scratches only the surface.

If you really insist in storing database-like data in an arithmetic calculator grid, you can simply edit the sheet in place (which requires some spreadsheet know-how) or you may install LibreOffice instead of OpenOffice: https://www.libreoffice.org/discover/li ... penoffice/
The LibreOffice developers added a Calc form that works on a basic level. At least it enters correct dates. However, it fail on so many other points that I never used it. For instance, it does not update any references because it does not insert new rows for new records. It appends new records. This way any formula such as =SUM(A1:A99) remains the same when you insert (or delete) records with that form.