Date Difference formula

Date difference refers to the duration between two dates, measured in days, months, or years. It is a useful calculation in various scenarios, such as tracking project timelines or calculating employee tenure.

To calculate the date difference between two dates in Google Sheets using Apps Script, we can use the following custom formula:

function dateDiff(start, end, unit) {
  var startDate = new Date(start);
  var endDate = new Date(end);
  var diff = endDate.getTime() - startDate.getTime();

  switch (unit) {
    case "days":
      return Math.floor(diff / (1000 * 60 * 60 * 24));
      break;
    case "months":
      return (endDate.getFullYear() - startDate.getFullYear()) * 12 + (endDate.getMonth() - startDate.getMonth());
      break;
    case "years":
      return endDate.getFullYear() - startDate.getFullYear();
      break;
    default:
      return "Invalid unit";
      break;
  }
}

The custom formula takes three arguments – start date, end date, and unit of calculation (days, months, or years). It then converts the input dates to Date objects and calculates the difference in milliseconds using the getTime() method. The difference is then divided by the appropriate factor to obtain the duration in the specified unit.

To use this custom formula, simply enter it into a cell and provide the required inputs in the appropriate format (e.g. “yyyy-mm-dd”). For example, to calculate the number of days between January 1, 2022 and March 31, 2022, we can use the formula:

=dateDiff("2022-01-01", "2022-03-31", "days")

This will return the result “89”.