Date format

Discussion in 'General Questions' started by Stephen Vetter, Jul 3, 2017.

Date format

  1. Stephen Vetter

    Stephen Vetter New Member

    Joined:
    Jun 24, 2017
    Messages:
    11
    Likes Received:
    2
    Hi,

    I am starting to play more extensively with MarketXLS to replace Yahoo functions and (another data source).

    However, dates output by MarketXLS do not play nicely with my date fields.

    I use ISO 8601 date format (yyyy-mm-dd), see https://en.wikipedia.org/wiki/ISO_8601, for all dates.

    Some dates output by MarketXLS work properly to follow .xlsx date formats, others stubbornly remain in m/d/yyyy format.

    Out of the following examples - only DividendDate() and last_split_date() work properly to follow .xlsx date formats ...


    Symbol: AAPL
    DividendDate(Symbol) 2017-05-18
    DividendPayDate(Symbol) 5/18/2017
    Ex_DividendDate(Symbol) 5/11/2017
    ExDividendDate(Symbol) 2017-05-11 00:00:00.0
    LastTradeDate(Symbol) 6/30/2017
    QuoteDate(Symbol) 6/30/2017
    last_split_date(Symbol) 2014-06-09

    Greatly appreciate if this can be resolved.

    Kind regards, Stephen Vetter
     
  2. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Stephen,

    You should be able to format the results returned from marketxls with excel functions to format the dates the way you like.
     
  3. Stephen Vetter

    Stephen Vetter New Member

    Joined:
    Jun 24, 2017
    Messages:
    11
    Likes Received:
    2
    Hi - Thank you for replying. However, MarketXLS is not working properly with dates.

    If I format a cell as number format,e.g. ####.# and run the standard Excel function =NOW(), as of a few minutes ago this returned the value "42938.983403588", where the whole number (42938) is the date and decimal part (.983403588) is the time. I can then display any such number using any date & time format that I want. However MarketXLS seems to be putting out a text string for date values.

    Please see attached file. Column A is selected date-related functions. Column B is the function call, with "Symbol" = cell B1 (i.e. AAPL), "StartDate" = cell B14 (i.e. 2017-01-01), "EndDate" = cell B15 (i.e. TODAY()). Column B cells are formatted as "yyyy-mm-dd". Column C cells are formatted as "####.#" to 10 decimal places. None of the MarketXLS function outputs are respecting either the Column B or Column C formats.

    Kind regards, Stephen Vetter
     

    Attached Files:

  4. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi Stephen,

    Can we not just right click on the dates and click on format and then select dates? Like the way I have done below? I get issue around the ex-dividend date though.

    upload_2017-7-30_15-55-53.png

    Kind regards
    Ankur
     
  5. Stephen Vetter

    Stephen Vetter New Member

    Joined:
    Jun 24, 2017
    Messages:
    11
    Likes Received:
    2
    Hi Ankur, Thanks for looking at this... please bear with me.

    All date and time functions should produce a result as a decimal number, as follows:

    From Microsoft support website
    Date is defined as:
    Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. You will need to change the number format (Format Cells) in order to display a proper date.​

    From Microsoft support website
    time is defined as:
    The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).​


    In the example I provided, please format column B as yyyy-mm-dd hh:mm:ss and column C as a decimal number with 10 digits. The rows with the =TODAY() and =NOW() functions should return your current date & time as follows
    upload_2017-8-5_12-11-23.png

    All MarketXLS functions should output in the same way.


    Kind regards, Stephen
     

Share This Page