locked
Excel: problem working with date/time information RRS feed

  • Question

  •  

    Given a binding to a table that contains data in date/time format, a call to retrieve the data returns the information in Excel's "serial number" format.  For example, a cell value of "5/19/2013 21:29" is returned as "41413.895150462966".   The associated code is:

    binding.getDataAsync({ coercionType: Office.CoercionType.Table, valueFormat: Office.ValueFormat.Unformatted }, function (asyncResult) { ... });

    I want to use the 'unformatted' value format, to avoid localization issues.     Now I am struggling to parse the serial number format, to obtain the date information.

    I notice that the alternate scenario of writing date information to the document from script works well;  you can use javascript Date objects in calls to setSelectedDataAsync.  So there is a lack of symmetry.

    I suggest that actual Date objects be returned from calls to getDataAsync(valueFormat:Unformatted). 

    Is there a convenient way to parse the serial number format?

    Thanks

     

     


    Eron Wright - Azure Services Developer Portal Team

    • Changed type Quist Zhang Tuesday, May 28, 2013 2:59 AM
    Monday, May 20, 2013 4:32 AM

Answers

  • Eron,

    Try this to Convert Excel dates into JS date objects:

    new Date((bindingData.rows[index][3] - (25567 + 1)) * 86400 * 1000);

    It has worked for me.

    Thanks.

    • Proposed as answer by Quist Zhang Tuesday, May 28, 2013 2:59 AM
    • Marked as answer by Quist Zhang Wednesday, May 29, 2013 10:51 AM
    Friday, May 24, 2013 2:39 PM
  • Hi Eron,

    Thank you for posting in the MSDN Forum.

    Excel stores dates as the number of days from 1 Jan 1900.

    If you're woking with C#, you'll need to use the returned value as a parameter of  DateTime.FromOADate() to get a real datatime value. Sorry for that I don't have too much insight into JavaScript, but I believe you can find the JavaScript counterpart of this method.

    Hope it helps.

    Best regard,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Quist Zhang Tuesday, May 28, 2013 2:59 AM
    • Marked as answer by Quist Zhang Wednesday, May 29, 2013 10:51 AM
    Tuesday, May 21, 2013 1:39 PM

All replies

  • Hi Eron,

    Thank you for posting in the MSDN Forum.

    Excel stores dates as the number of days from 1 Jan 1900.

    If you're woking with C#, you'll need to use the returned value as a parameter of  DateTime.FromOADate() to get a real datatime value. Sorry for that I don't have too much insight into JavaScript, but I believe you can find the JavaScript counterpart of this method.

    Hope it helps.

    Best regard,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Quist Zhang Tuesday, May 28, 2013 2:59 AM
    • Marked as answer by Quist Zhang Wednesday, May 29, 2013 10:51 AM
    Tuesday, May 21, 2013 1:39 PM
  • Eron,

    Try this to Convert Excel dates into JS date objects:

    new Date((bindingData.rows[index][3] - (25567 + 1)) * 86400 * 1000);

    It has worked for me.

    Thanks.

    • Proposed as answer by Quist Zhang Tuesday, May 28, 2013 2:59 AM
    • Marked as answer by Quist Zhang Wednesday, May 29, 2013 10:51 AM
    Friday, May 24, 2013 2:39 PM