Calculating year, month, days between dates in google apps script

Question

I have calculated using the below function and it gives the o/p in the format of "X YEARS, Y MONTHS, Z DAYS" and for some dates its giving some wrong o/p. I think I did some calculation missing in the formulas.

The function is,

/**
 * @param {Date} startdate
 * @param {Date} enddate
 * @return {String}
 */
function leasePeriodCalc(startDate,endDate)
{
  var sdate=startDate;
  var edate=endDate;
  edate.setDate( edate.getDate()+1);
  edate=new Date(edate);
  if(sdate.valueOf()>edate.valueOf()){
    return('0');
  }
  else{
    var years=((((edate.getDate()-sdate.getDate())<0 ? -1:0)+((edate.getMonth()+1)-(sdate.getMonth()+1)))< 0 ? -1 : 0)+(edate.getFullYear()-sdate.getFullYear());
    var months=((((edate.getDate()-sdate.getDate())<0 ? -1:0)+((edate.getMonth()+1)-(sdate.getMonth()+1)))< 0 ?12:0)+((edate.getDate()-sdate.getDate())<0 ? -1:0)+((edate.getMonth()+1)-(sdate.getMonth()+1));
    if((edate.getMonth()-1)!=1.0)
    {
      var days=((edate.getDate()-sdate.getDate())< 0 ?new Date(edate.getFullYear(), edate.getMonth(),0).getDate():0)+(edate.getDate()-sdate.getDate());
    }
    else
    {
      var days=((edate.getDate()-sdate.getDate())< 0 ?new Date(edate.getFullYear(), edate.getMonth()+1,0).getDate():0)+(edate.getDate()-sdate.getDate());
    }
    var day;
    var month;
    var year;
    if(years>1)year= years+ 'Years';
    else year=years+'Year';
    if(months>1) month= months+ 'Months';
    else month=months+'Month';
    if(days>1) day= days+ 'Days';
    else day=days+'Day';
    if(years==0&&months!=0&&days!=0) return(month+', '+day);
    else if(years!=0&&months==0&&days!=0) return(year+', '+day);
    else if(years!=0&&months!=0&&days==0) return(year+', '+month);
    else if(years==0&&months==0&&days!=0) return(day);
    else if(years==0&&months!=0&&days==0) return(month);
    else if(years!=0&&months==0&&days==0) return(year);
    else if(years==0&&months==0&&days==0) return(day);
    else if(years!=0&&months!=0&&days!=0) return(year+', '+month+', '+day);
  }
}

if you gives the i/p as below it returning the false o/p:

28th feb 2013 - 28th feb 2014

Expected o/p : 1 YEAR , 1 DAY

Given o/p : 1 YEAR , 4 DAYS

But if I select 28th feb 2013 - 27th feb 2014 means, It gave the correct o/p:

Expected o/p : 1 YEAR

Given o/p : 1 YEAR

Please advice to correct my fault if I did anything.

And also I have to tell that I'm not setting the rules n all. In general a month is calculating as per the days lying on the month.

For example, If we get a loan from a bank we ll pay the interest per month only even that month may have 30 days or 29 days or 28 days or 31 days.

And also if we take a room for monthly rental means, We ll pay the rent per month only rite? even it can be from 20th March - 19th April. Even it contains 31 days it is said to be one month only. Please help me to conclude this.

Tnx, CL.

Answer 1

For complex date/time manipulations in JavaScript I find that the Moment.js library can really help. I wrapped it up into an Apps Script library, which you can include using the project key MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48. I used it to take a crack at this problem, although I may have missed some edge cases.

// Load the library (key: MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48).
var moment = Moment.load();

function getDuration(startDate, endDate) {
  var start = moment(startDate);
  var end = moment(endDate);
  var units = ['years', 'months', 'days'];
  var parts = [];
  units.forEach(function(unit, i) {
    var diff = Math.floor(end.diff(start, unit, true));
    if (diff > 0 || i == units.length - 1) {
      end.subtract(unit, diff);
      parts.push(diff + ' ' + unit);
    }
  })
  return parts.join(', ');
}

Answer 2

You can include moment.js as an external dependancy and use it inside Google App Script project. Refering to https://stackoverflow.com/a/45231921/5429123 article, you can do something like this.

eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.22.2/moment.min.js').getContentText());
var date = moment().format("MMM Do YY");
Logger.log(date)

This will get you the last version of moment.js (2.22.2 as of 2018/10/25), compared to the old 2.4.0 available as a GAS library (like the accepted answer suggests).

PS - This works!

Answer 3

Following Code Work for me Make sure u associate moment.js in script before executing

  var moment = Moment.load()

  var StartDate= new Date(StartDate1);
  var DD1 = StartDate.getDate();    
  var MM1 = StartDate.getMonth(); 
  var MM1=MM1+1                 //As Month Start with Zero
  var YYYY1 = StartDate.getYear();

  var EndDate= new Date(EndDate1);
  var DD = EndDate.getDate();    
  var MM = EndDate.getMonth(); 
  var MM=MM+1                 //As Month Start with Zero
  var YYYY = EndDate.getYear();



  var a = moment([YYYY, MM, DD]);
  var b = moment([YYYY1, MM1, DD1]);
  return a.diff(b, 'days')
  Logger.log(a.diff(b, 'days') )

Answer 4

Second attempt:

function periodCalc(startDate, endDate) {

  var output = '';

  var sYear = startDate.getFullYear();
  var sMonth = startDate.getMonth();
  var sDay = startDate.getDate() - 1;
  var eYear = endDate.getFullYear();
  var eMonth = endDate.getMonth();
  var eDay = endDate.getDate();

  var tMonths = eYear * 12 + eMonth - sYear * 12 - sMonth;
  var days = eDay - sDay;

  if (days < 0) {
    tMonths--;
    var sDate = new Date(sYear, sMonth + tMonths, sDay);
    var eDate = new Date(eYear, eMonth, eDay);
    days = (eDate.getTime() - sDate.getTime()) / 86400000;
  }

  if (tMonths < 0) return '0';
  var months = tMonths % 12;
  var years = (tMonths - months) / 12;

  output += (years == 0 ? '' : (', ' + years + ' Year' + (years == 1 ? '' : 's')));
  output += (months == 0 ? '' : (', ' + months + ' Month' + (months == 1 ? '' : 's')));
  output += (days == 0 ? '' : (', ' + days + ' Day' + (days == 1 ? '' : 's')));

  return output.substr(2);
}

However an issue remains when the start date is the first day of a month, and the end date is the last day of the month. So for example, 1 Jan 2013 to 31 March 2013 will return 2 months, 31 days. Is this the desired result?

---

Edit: first attempt (which is flawed, see first comment):

I must admit my brain was hurting a bit looking at the code, which I'm sure would only have a minor error in logic somewhere. I thought it would be quicker if I tried re-writing it from scratch, but I think it would need some further testing (it works with my limited testing so far):

function periodCalc(startDate, endDate) {
  var output = '';
  endDate.setFullYear(endDate.getFullYear(), endDate.getMonth(), endDate.getDate() + 1);
  var sYear = startDate.getFullYear();
  var sMonth = startDate.getMonth();
  var sDay = startDate.getDate();

  var eDay = endDate.getDate(); 
  var days = eDay - sDay;
  if (days < 0) {
    var eopm = new Date(endDate);
    eopm.setDate(0);
    days = eDay + eopm.getDate() - sDay;
    endDate.setDate(eDay - days);
  }

  var eMonth = endDate.getMonth();
  var months = eMonth - sMonth;
  if (months < 0) {
    months = eMonth + 12 - sMonth;
    endDate.setMonth(eMonth - months);
  }

  var eYear = endDate.getFullYear();
  var years = eYear - sYear;
  if (years < 0) return '0';

  output += (years == 0 ? '' : (', ' + years + ' Year' + (years == 1 ? '' : 's')));
  output += (months == 0 ? '' : (', ' + months + ' Month' + (months == 1 ? '' : 's')));
  output += (days == 0 ? '' : (', ' + days + ' Day' + (days == 1 ? '' : 's')));

  return output.substr(2);
}

Answer 5

Here is something I wrote a while ago, it's not fully tested but think it should do what it's supposed to do

[UPDATE]: fixed the bug but still acting funny when low date is feb 29 and diff is more than one year

function dateDiff(a,b){
    var low = (a>b)?b:a,
    heigh = (a>b)?a:b,
    diff = {
        years:0,
        months:0,
        days:0
    },
    tmpDate,
    tmpMonth;
    //if you'd like the diff to be including the the last day
    // of the end date you can do: lob.setDate(low.getDate()+1);
    if(low==heigh){return diff;}//a===b no difference
    diff.years=heigh.getFullYear()-low.getFullYear();
    tmpDate=new Date(low.getTime());
    tmpDate.setFullYear(low.getFullYear()+diff.years);
    if(tmpDate>heigh){
      diff.years--;
    }
    low.setFullYear(low.getFullYear()+diff.years);
    tmpMonth=heigh.getMonth()-low.getMonth();
    diff.months=(tmpMonth<0)?tmpMonth+12:tmpMonth;
    tmpDate=new Date(low.getTime());
    tmpDate.setMonth(low.getMonth()+diff.months);
    if(tmpDate>heigh){
      diff.months--;
    }
    low.setMonth(low.getMonth()+diff.months);
    while(low<heigh){
        low.setDate(low.getDate()+1);
        diff.days++;
        if(low>heigh){
          low.setDate(low.getDate()-1);
          diff.days--;
          break;
        }
    }
    return diff;
}


var a = new Date(2013,9,30);
var b = new Date(2014,1,26);
console.log(dateDiff(a,b));
//next one is a bit buggy, one might say it's
// 1 year and 1 day if considoring end of feb 2000
// to 1st of mar 2001
var a = new Date(2000,1,29);
var b = new Date(2001,2,1);
console.log(dateDiff(a,b));

Answer 6

Other alternative of Date.Diff. xDate is a wrapper over Date Javascript object:

//-----------------------------------------------------------------------------
// Date Wrapper
//-----------------------------------------------------------------------------
var kDate = function () {
    "use strict";
    var dat = NaN, that = this;
    switch (arguments.length) {
    case 0: dat = new Date(); break;
    case 1: dat = new Date(arguments[0]); break;
    default:
        dat = new Date( arguments[0] || null, arguments[1] || null, arguments[2] || null, arguments[3] || null, 
                        arguments[4] || null, arguments[5] || null,  arguments[6] || null);
    }
    Object.getOwnPropertyNames(Date.prototype).forEach(function(prop) {
        that[prop] = function () { 
            return dat[prop].apply(dat, arguments); 
        };
    });
    return this;
};
Object.getOwnPropertyNames(Date).forEach(function(prop) {
    if (["length", "name", "arguments", "caller", "prototype"].indexOf(prop) < 0) {
        kDate[prop] = Date[prop];
    }
});

kDate.MAXDATE = 8640000000000000;
kDate.MINDATE = -8640000000000000;
kDate.YEARZERO = -62167132800000;
kDate.YEAR = 31536000000;
kDate.MONTH = 2592000000;
kDate.DAY = 86400000;
kDate.HOUR = 3600000;
kDate.MINUTE = 60000;
kDate.SECOND = 1000;

//-----------------------------------------------------------------------------
// kDate.diff()
//-----------------------------------------------------------------------------
kDate.diff = function(date1, date2) {
    var d1, d2;
    d1 = kDate.MAXDATE + (typeof date1 === 'number' ? date1 : date1.getTime());
    d2 = kDate.MAXDATE + (typeof date2 === 'number' ? date2 : date2.getTime());
    diff = new kDate(NaN);
    diff.diffDate1 = new kDate(typeof date1 === 'number' ? date1 : date1.getTime());
    diff.diffDate2 = new kDate(typeof date2 === 'number' ? date2 : date2.getTime());
    diff.diffTotalMilliseconds = d2 < d1 ? d1 - d2 : d2 - d1;
    diff.setTime(kDate.YEARZERO + diff.diffTotalMilliseconds);
    diff.diffTotalSeconds = diff.diffTotalMilliseconds / kDate.SECOND;
    diff.diffTotalMinutes = diff.diffTotalMilliseconds / kDate.MINUTE;
    diff.diffTotalHours =   diff.diffTotalMilliseconds / kDate.HOUR;
    diff.diffTotalDates =   diff.diffTotalMilliseconds / kDate.DAY;
    diff.diffYears =        diff.diffDate1.getUTCFullYear() - diff.diffDate2.getUTCFullYear();
    diff.diffMonth =        diff.diffDate1.getUTCMonth() - diff.diffDate2.getUTCMonth();
    diff.diffDate =         diff.diffDate1.getUTCDate() - diff.diffDate2.getUTCDate();
    diff.diffHours =        diff.diffDate1.getUTCHours() - diff.diffDate2.getUTCHours();
    diff.diffMinutes =      diff.diffDate1.getUTCMinutes() - diff.diffDate2.getUTCMinutes();
    diff.diffSeconds =      diff.diffDate1.getUTCSeconds() - diff.diffDate2.getUTCSeconds();
    diff.diffMilliseconds = diff.diffDate1.getUTCMilliseconds() - diff.diffDate2.getUTCMilliseconds();
    return diff;
};
kDate.prototype.diff = function (date) {
    return kDate.diff(this, date);
};

Answer 7

Check out the example here: http://ditio.net/2010/05/02/javascript-date-difference-calculation/

Much cleaner code and easier to read. (and it works!) ;-)