[Kst] Feature Request: Time axis in Excel numeric units

[Barth Netterfield]

On Tue, May 22, 2012 at 9:02 AM, repaul_schrott-kst at yahoo.de
<repaul_schrott-kst at yahoo.de> wrote:
> sorry for the late reply. I can provide you several links, some from Microsoft Office.com help pages and a blog entry by Joel Sposkly
>     http://www.joelonsoftware.com/items/2006/06/16.html

Fun story, but doesn't answer the main question.

>     -  Windows uses ("1.1.1900 00:00:00" = 1), Mac uses ("1.1.1904 00:00:00" = 0) as the reference date, meaning the Julian date formula given below is correct for Windows as it references 31.12.1899 00:00:00 = 0. However one can change from 1900 to 1904 in the Excel preferences for individual sheets. It is fairly intransparent. I checked on my Mac and indeed Mac Excel adds "4 years + 1 day" compared to Windows. Thus I need to change my preferences -- what a mess !

So we need two, Excel (1899/Windows) and Excel (1904/Mac).

>
>     - The basline for Excel / Basic / NT is the Julian date system. I don't know the exact rules how this converted to local time / timezone representations but I guess that you have the same problem with "C Time" and QT representations. I can imagine that it depends on the operating system time settings but this shouldn't be different than for Linux.

C Time is seconds since 1.1.1970 00:00:00 GMT.  So if you change the
timezone of the display program, the time displayed will change.

>
>
>     - In the CSV file the date is stored as the Julian date floating point number. Therefore the interpretation is always with respect to what the local OS and its rules are.

?

On the other hand, at least in some cases, Windows uses time since the
Epoch in Local Time.  We need to know which excel uses.  Any
references at Microsoft which address this issues would be great to
have.

If you change the timezone on your system, does Excel change the time
it reports when it loads a csv?


>
> Cheers
>
> Ralph
>
>
> ----- Ursprüngliche Message -----
> Von: Barth Netterfield <netterfield at astro.utoronto.ca>
> An: "repaul_schrott-kst at yahoo.de" <repaul_schrott-kst at yahoo.de>; kst at kde.org
> CC:
> Gesendet: 18:39 Mittwoch, 16.Mai 2012
> Betreff: Re: [Kst] Feature Request: Time axis in Excel numeric units
>
> Hmmm...
>
> I implemented this - but realized there is at least one issue: This
> will cause kst to assume that the EXCEL_DATE is in GMT.  However Excel
> assumes that EXCEL_DATE is in local time.  The 'work-around' would be
> to just tell the plot axis that your TZ is GMT.  But this could be
> pretty confusing.
>
> Are there any more authoritative references about how excel does store
> date/times in csv files?
>
> cbn
>
> On Tue, May 15, 2012 at 4:55 AM, repaul_schrott-kst at yahoo.de
> <repaul_schrott-kst at yahoo.de> wrote:
>> I had a look at the sourcecode of plotaxis.[h,cpp] source code.
>>
>> The required formula is actually much simpler
>>
>>     JULIAN_DATE = EXCEL_DATE + 2415018.5
>> or
>>
>>     JD18991230  = 2415018.5   // Julian Date of 30-Dec-1899 00:00:00 GMT
>>
>>     JULIAN_DATE = EXCEL_DATE + JD1899_12_30
>>
>>
>> Thanks
>>
>> Ralph Paul
>>
>> _______________________________________________
>> Kst mailing list
>> Kst at kde.org
>> https://mail.kde.org/mailman/listinfo/kst
>
>
>
> --
> C. Barth Netterfield
> University of Toronto
> 416-845-0946
>
> _______________________________________________
> Kst mailing list
> Kst at kde.org
> https://mail.kde.org/mailman/listinfo/kst



-- 
C. Barth Netterfield
University of Toronto
416-845-0946