/****************************************************************************
*                                                                           *
*   EXCEL2DT.SPL Copyright 2014-2016 (C) DSP Development Corporation        *
*                                                                           *
*   Author:      Randy Race                                                 *
*                                                                           *
*   Synopsis:    Converts EXCEL seconds to Julian date and seconds          *
*                                                                           *
*   Revisions:   22 Aug 2014     RRR     Creation                           *
*                12 Mar 2015     RRR     "Real Time" units support          *
*                11 May 2016     RRR     Roundoff fix, from J. Stutz        *
*                                                                           *
****************************************************************************/


#if @HELP_EXCEL2DT

    EXCEL2DT

    Purpose: Converts EXCEL timestamps to Julian date and clock seconds.

    Syntax:  EXCEL2DT(xldt)

             (d, t) = EXCEL2DT(xldt)

                     xldt - A scalar or series of EXCEL format timestamps.

    Returns: A two column series of dates and clock time.

             (d, t) = excel2dt(xldt) returns the dates and clock
             time in two separate variables.

             If the input is a scalar, the return value is a date/time
             string.
            
    Example:
             excel2dt(36161.1510)

             Returns the string "1-01-1999 3:37:26.400".

    Example:
             W1: {36161.1510, 36270.1250, 36252.1752}
             W2: excel2dt(W1)

             W2 contains the date and time values:

              1-01-1999   3:37:26.400
              4-20-1999   3:00:00.000
              4-02-1999   4:12:17.280

    Example:
             W1: {36161.1510, 36270.1250, 36252.1752}
             W2: excel2dt(w1)
             W3: dt2excel(w2)

             Same as above except W3 == W1, the original Excel timestamp
             values.

    Example:
             W1: {36161.1510, 36270.1250, 36252.1752}
             W2: (d, t) = excel2dt(w1);ravel(d, t)
             W3: dt2excel(w2)

             Same as above except the date and time values are returned 
             as two separate series.

    Remarks:
             An EXCEL timestamp is a real value that represents the
             number of days that have elapsed since 12-30-1899. The
             integer part is the whole number of days and the
             fractional part is the fraction of a day.
         
             See DT2EXCEL to convert a Julan date and clock time to an
             Excel timestamp.

    See Also:
             DT2EXCEL
             DT2UNIX
             DTXY
             JULSTR
             LOCAL2UTC
             STRJUL
             STRTOD
             TODSTR
             UNIX2DT
             UTC2LOCAL
             XYDT
#endif


/* convert Excel timestamp to date/time */
ITERATE excel2dt(xldt)
{
        local days, jul, secs, dt, vunits, frac, utoff;

        if (argc < 1) error(sprintf("%s - input series required", __FUNC__));

        frac = xldt - int(xldt);

        /* Real Time if total time has fractional part */
        vunits = any(frac % 1) ? "Real Time" : "Time";

        /* excel timestamp days part */
        days = int(xldt);

        /* offset by excel julian start date */
        jul = days + julstr("1899/12/30");

        /* seconds from fraction of days */
        secs = (xldt - days) * 86400;

        if (isscalar(xldt))
        {
                if (outargc > 1)
                {
                        return(jul, secs);
                }
                else
                {
                        tstamp = sprintf("%s %s", strjul(jul), strtodmsec(secs));
                        return(tstamp);
                }
        }

        setmatrix(jul, 0);
        setvunits(jul, "Daily");

        setmatrix(secs, 0);
        setvunits(secs, vunits);

        /* set date of time series to first date */
        setdate(secs, strjul(jul[1]));

        if (outargc > 1)
        {
                return(jul, secs);
        }
        else
        {
                /* return as table */
                dt = ravel(jul, secs);
                setplotstyle(dt, 4);

                return(dt);
        }
}