Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

date type : incorrect values when export/copy #355

Open
PhilLAL opened this issue May 27, 2016 · 15 comments
Open

date type : incorrect values when export/copy #355

PhilLAL opened this issue May 27, 2016 · 15 comments
Labels
bug

Comments

@PhilLAL
Copy link

@PhilLAL PhilLAL commented May 27, 2016

Hi,
when using dates into an instance of ethercalc, I discovered that the export or copy of column of dates do not work (https://framacalc.org/y9uy2MxS76).

Indeed, whether we export into xls or csv format or copy the content of a cell, the content of the cell B1, which is "1/1/1990", is converted into "32874".

When looking into the CSV file, it seems the date type data are simply saved as an integer type data.
Solution could be to replace the date "1/1/1990" or "1990-01-01" by "1990-01-01 00:00:00 000" (ISO format), as into C1 cell, it seems to keep format when copied.
However, the content of this cell is not usable as a date type and can not be used as a date (for instance into a chart when exported).

In an opposite way, copying dates from an XLSX file into an ethercalc keep date type correct and usable.

@eddyparkinson

This comment has been minimized.

Copy link
Collaborator

@eddyparkinson eddyparkinson commented May 28, 2016

Export to CSV - formating errors also (XLSX ?)

these export as a number rather than the display value::
2016/5/1 exports as 42491.333333333336
8:56:0 exports as 0.37222222222222223
10% exports as 0.1

they are all stored as constant, e.g.
set D5 constant n% 0.1 10%
set D6 constant nd 41922 10/10/2014

coord: "B10", datavalue: 41307, datatype: "c", formula: "2013/2/2", valuetype: "nd"

@PhilLAL

This comment has been minimized.

Copy link
Author

@PhilLAL PhilLAL commented Jun 2, 2016

I wonder if there is any way to use a specific format for cells to allow export to be readable into libreoffice for instance.
Any idea ?

eddyparkinson added a commit to eddyparkinson/cellmaster that referenced this issue Feb 11, 2017
adds feature to export formulas.

Tested with
http://sheet.cellmaster.com.au/export



These can also now be fixed by changing. harb.js and xlsx.js in https://github.com/quilt-js
Formulae From CSV Interpreted as String Literals, not as Formulae audreyt#304
date type : incorrect values when export/copy audreyt#355

Signed-off-by: Eddy Parkinson <eddyparkinsoncity@yahoo.co.uk>
@andreasmerkel

This comment has been minimized.

Copy link

@andreasmerkel andreasmerkel commented Nov 8, 2017

Hi,
I think I have a similar problem. So far, it was possible to calculate with dates, e.g. when using an agenda: there is a starting time and a duration for an item of the agenda, so we just add those two cells and got the time for the second item of the agenda. Then I exported the agenda to csv.
After updating to the last release (20170704), the csv-export just prints numbers instead of times (e.g. '0.013888888888888888' instead of '0:20' (minutes) ).
Is there a workaround, so I can use time and dates again for calculating and exporting them to csv?
Best regards,
Andreas

@eddyparkinson

This comment has been minimized.

Copy link
Collaborator

@eddyparkinson eddyparkinson commented Nov 8, 2017

Date export workaround

Export a Date or time in CSV format as text rather than a number

B3 = 0:20:00
=""&B3

https://ethercalc.org/5wc93efiqvkg

e.g. it exports CSV as

,0.013888888888888888,0:20:00

@andreasmerkel

This comment has been minimized.

Copy link

@andreasmerkel andreasmerkel commented Nov 9, 2017

Hi,
thank you very much for your help! I tried the workaround and it works even when calculating with dates (tried in your ethercalc).
Best, Andreas

@pierre-haessig

This comment has been minimized.

Copy link

@pierre-haessig pierre-haessig commented Oct 17, 2018

As a side note, since I wanted to process a CSV file export containing dates in this internal Ethercalc format, I found that the number corresponds to a number of days since 1899-12-30 (very close to 1900-01-01, but not exactly... slightly odd).

Here is a an example snippet to get back the date 2018-10-16 which is exported as 43389 by Ethercalc (more precisely, the current https://lite.framacalc.org instance):

import datetime
td = datetime.timedelta(43389)
d0 = datetime.date(1899, 12, 30) # apparently the origin of time for Ethercalc
d = d0 + td

This gives the object datetime.date(2018, 10, 16) which can then be converted to any string representation with strftime. Getting the ISO format '2018-10-16' is even simpler with d.isoformat().

I gues this code can be adapted to any language. However, some tuning is needed to process fractional timestamps.

@eddyparkinson

This comment has been minimized.

Copy link
Collaborator

@eddyparkinson eddyparkinson commented Oct 18, 2018

@pierre-haessig

the year 1900 is not a leap year

This is a well know issue with spreadsheet dates.

Note Dan Bricklin invented the spreadsheet - we use his code https://github.com/DanBricklin/socialcalc

DATE(year, month, day)
Returns the appropriate date value given numbers for year, month, and day. For example: DATE(2006,2,1) for February 1, 2006. Note: In this program, day "1" is December 31, 1899 and . Some programs use January 1, 1900, as day "1" and treat 1900 as a leap year. In both cases, though, dates on or after March 1, 1900, are the same.

@PhilLAL

This comment has been minimized.

Copy link
Author

@PhilLAL PhilLAL commented Jan 26, 2019

Date export workaround

Export a Date or time in CSV format as text rather than a number

B3 = 0:20:00
=""&B3

https://ethercalc.org/5wc93efiqvkg

e.g. it exports CSV as

,0.013888888888888888,0:20:00

Hi, Eddy, I just tryed and it does not seem to work. I think I made it work when you answered. Do you have any idea why it fails?
Regards

@eddyparkinson

This comment has been minimized.

Copy link
Collaborator

@eddyparkinson eddyparkinson commented Jan 27, 2019

@PhilLAL Can you try again, maybe use https://ethercalc.org and share here.

@PhilLAL

This comment has been minimized.

Copy link
Author

@PhilLAL PhilLAL commented Jan 27, 2019

@eddyparkinson Thank you, I tried again, entering dates in row 1, it does not work.

@eddyparkinson

This comment has been minimized.

Copy link
Collaborator

@eddyparkinson eddyparkinson commented Jan 27, 2019

It works for me, I wonder what is different in your setup.

exporting https://ethercalc.org/5wc93efiqvkg as CSV gives:

,,,,
,,,,
,0.013888888888888888,0:20:00,,
43254,,,,
,,,,
43252,,,,
,,,,
42862,,,,
,0.5,0.013888888888888888,12:00:00,0:20:00
,0.5138888888888888,,12:20:00,

@PhilLAL

This comment has been minimized.

Copy link
Author

@PhilLAL PhilLAL commented Jan 28, 2019

Ouch, I got a 502 bad gateway error so I can't test now.
If I get the last exported CSV and I open it with libreoffice, I got the following choice for text import into the first row:

43254
43252
42862

Default row type is Standard, when I choose text, it does not change anything.
Here is what I see into libreoffice opened file:

	0.013888888888888888	00:20:00		
43254				
				
43252				
				
42862				
	0.5	0.013888888888888888	12:00:00	00:20:00
	0.5138888888888888		12:20:00	

I also tried several raw format like Date (DMY) and Date (MDY) since I have a libreoffice configured in french. I also changed my local configuration.
When I use raw format Date (MDY), I got an error for third date (which is 07/05/2017 so I don't understand why).
FYI, I run libreoffice 5.1.6.2 on ubuntu 16.04.

@eddyparkinson

This comment has been minimized.

Copy link
Collaborator

@eddyparkinson eddyparkinson commented Jan 29, 2019

Dates to libreoffice

Opions:

  • Export as excel and then apply a date format to the cell. - This works with open office, looks like it would work in libreoffice.
  • Convert the date to a string in ethercal. e.g. =""&B3 Then, and export as CSV - good if you just need the see the date, but it would not export formulas. See example above.
  • fix the export code, so it exports dated in excel format.
@PhilLAL

This comment has been minimized.

Copy link
Author

@PhilLAL PhilLAL commented Jan 29, 2019

Thank you Eddy, option 1 works for me :)

@Erudition

This comment has been minimized.

Copy link

@Erudition Erudition commented May 28, 2019

Option 1 worked for me too. phew!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
5 participants
You can’t perform that action at this time.