datetime - OADate to Milliseconds timestamp in Javascript - Stack Overflow

admin管理员组

文章数量:1410730

I try to do the exact opposite of :

What is equivalent of DateTime.ToOADate() in javascript?

Create a millisecond date (number of milliseconds since 1/1/1970) from a OADate (number of days since 30 dec 1899 as a double value)

my guess is I can do like this :

this.unixTimeStampFromOADate = function( OADateFloat)
{
        var oaDateFor1970 = ?? ; //what's the value for 1/1/1970 in OADate format ?
        var delta = Math.floor(OADateFloat - oaDateFor1970);

        return delta*(1000*60*60*24); 
}

so if I'm right, I need the value for 1/1/1970 in OADate format. if I'm wrong, please can you suggest another conversion method ?

I try to do the exact opposite of :

What is equivalent of DateTime.ToOADate() in javascript?

Create a millisecond date (number of milliseconds since 1/1/1970) from a OADate (number of days since 30 dec 1899 as a double value)

my guess is I can do like this :

this.unixTimeStampFromOADate = function( OADateFloat)
{
        var oaDateFor1970 = ?? ; //what's the value for 1/1/1970 in OADate format ?
        var delta = Math.floor(OADateFloat - oaDateFor1970);

        return delta*(1000*60*60*24); 
}

so if I'm right, I need the value for 1/1/1970 in OADate format. if I'm wrong, please can you suggest another conversion method ?

• javascript
• datetime
• timestamp

Share Improve this question Follow edited May 23, 2017 at 11:54 CommunityBot 111 silver badge asked Mar 21, 2013 at 14:07 GuianGuian 4,72644 gold badges3737 silver badges5959 bronze badges 5

• have a look here stackoverflow./questions/7348805/… – Mark Walters Commented Mar 21, 2013 at 14:15
• Just pute it with the formula given in that question. If you pass in new Date(0), you get out the oaDate 25569 – Bergi Commented Mar 21, 2013 at 14:21
• 1 it should be the exact number of day from 1899 dec 30 to 1970 1/1. :/ – Guian Commented Mar 21, 2013 at 14:24
• @Mark its the exact opposite opperation I'm looking for – Guian Commented Mar 21, 2013 at 14:24
• oh yeah bergi thx so simple why didn't I think about it XD ^^ so are you agree with my function ? – Guian Commented Mar 21, 2013 at 14:26

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 6

Javascript dates use a time value that is milliseconds since 1970-01-01T00:00:00Z. The time value for the date 1899-12-30 is -2209197600000.

To get the days since then, get the milliseconds for midnight at the start of today, subtract it from the OA epoch, divide by the ms in one day and get the absolute value. Note that the time values are all UTC so daylight saving, leap years, etc. are accounted for.

var epoch = new Date(1899, 11, 30); // 1899-12-30T00:00:00
var now = new Date();               // 2013-03-22T<current time>
now.setHours(0,0,0,0)               // 2013-03-22T00:00:00

var oaDate  = Math.abs((epoch - now) / 8.64e7); // 41355 for 2013-03-22

You can test it against some dates here (note that those dates are in the confusing US m/d/yy format).

Edit

Sorry, got the sense backwards. Here are some functions to go both ways.

Also took some time to work out that where it says "OLE Automation date is implemented as a floating-point number whose integral ponent is the number of days before or after midnight, 30 December 1899" actually means on or after 1899-12-30 00:00:00 and that the "fractional ponent represents the time on that day divided by 24". In other words, while 1899-12-29 00:00:00 is -1, the value for `899-12-29 06:00:00 is -1.25, not -0.75.

Anyhow, these functions seem to work now, but please test thoroughly:

var toOADate = (function () {
  var epoch = new Date(1899,11,30);
  var msPerDay = 8.64e7;

  return function(d) {
    var v = -1 * (epoch - d)/msPerDay;

    // Deal with dates prior to 1899-12-30 00:00:00
    var dec = v - Math.floor(v);

    if (v < 0 && dec) {
      v = Math.floor(v) - dec;
    }

    return v;
  }
}());


var fromOADate = (function() {
  var epoch = new Date(1899,11,30);
  var msPerDay = 8.64e7;

  return function(n) {
    // Deal with -ve values
    var dec = n - Math.floor(n);

    if (n < 0 && dec) {
      n = Math.floor(n) - dec;
    }

    return new Date(n*msPerDay + +epoch);
  }
}());

var now = new Date();
var oaNow = toOADate(now);
var now2 = fromOADate(oaNow);

alert('Today: ' + now + '\nOADate: ' + oaNow + '\noaNow to Date: ' + now2);

The specification for OADate is confusing, particularly the way negative numbers are handled.

Edit Feb 2019

Updated version of functions, use local date values.

/* Convert a Microsoft OADate to ECMAScript Date
** Treat all values as local.
** @param {string|number} oaDate - OADate value
** @returns {Date}
*/
function dateFromOADate (oaDate) {
  // Treat integer part is whole days
  var days = parseInt(oaDate);
  // Treat decimal part as part of 24hr day, always +ve
  var ms = Math.abs((oaDate - days) * 8.64e7);
  // Add days and add ms
  return new Date(1899, 11, 30 + days, 0, 0, 0, ms);
}


/* Convert an ECMAScript Date to a Microsoft OADate
** Treat all dates as local.
** @param {Date} date - Date to convert
** @returns {Date}
*/
function dateToOADate (date) {
  var temp = new Date(date);
  // Set temp to start of day and get whole days between dates,
  var days = Math.round((temp.setHours(0,0,0,0) - new Date(1899, 11, 30)) / 8.64e7);
  // Get decimal part of day, OADate always assumes 24 hours in day
  var partDay = (Math.abs((date - temp) % 8.64e7) / 8.64e7).toFixed(10);
  return days + partDay.substr(1);
}

var now = new Date();
var x = dateToOADate(now);
console.log('Now: ' + now.toString());
console.log('As an OADate: ' + x);
console.log('Back to date: ' + dateFromOADate(x).toString());

window.onload = function(){ 
  var el = document.getElementById('in')
  el.addEventListener('change', function() {
    var oaDate = dateToOADate(new Date(new Date(el.value)));
    document.getElementById('out').value = oaDate;
    document.getElementById('out2').value = dateFromOADate(oaDate);
  });
}

input {width: 25em}

<table>
  <tr>
    <td>Input date:<br>(DD MMM YYYY HH:mm)
    <td><input id="in" value="29 Dec 1899 06:00">
  <tr>
    <td>OA Date:
    <td><input id="out" readonly>
  <tr>
    <td>Back to standard date:
    <td><input id="out2" readonly>
</table>

本文标签： datetimeOADate to Milliseconds timestamp in JavascriptStack Overflow