Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 757
chocka Avatar asked Apr 22 '13 14:04

chocka


People also ask

How do I calculate years between two dates in Google Sheets?

DATEDIF is a function in Google Sheets that calculates the difference between two dates. This function can return the difference value in years, months, or days. With the proper parameters, DATEDIF can also return the months and days difference with various quirks, such as ignoring the year difference.


1 Answers

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(', ');
}
like image 123
Eric Koleda Avatar answered Sep 29 '22 20:09

Eric Koleda