OADate to Milliseconds timestamp in Javascript

Question

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 ?

Answer 1

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