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

[repaul_schrott-kst at yahoo.de]

Hi Barth,

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

Anyway, there are at least several issues.

    -  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 !


    - 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.


    - 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.

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