Display Human-Readable Time Periods

I have logs of things and I want to show the “human” time period between two dates.

Very specifically, I have a training log for one of my sports. I wanted to show “You have been training for X years, Y months, and Z days”.

To solve this, I initially used a function. But I found the results unpleasant. So I decided to use a Google Apps Script to create something with a more aesthetic output.

The Google Sheets Function for Human Time Periods

If you want a quick and dirty solution to show a human-readable time period, you can use a function. This is simpler, less computationally expensive, and quicker to display.

The function is as follows:

=INT(B23/365)&" years, "&INT(MOD(B23,365)/30.44)&" months, and "&ROUNDUP(MOD(MOD(B23,365),30.44))&" days."

This works. But it has a number of aesthetic errors in the results.

It doesn’t change plurals. For example “1 years” or “2 years”.
It shows zero answers. For example, if it’s less than a year, the result says “0 years”.
It doesn’t quite make logical sense. For example, if the start and end dates are 1 September 2013 and 1 September 2023, the answer isn’t exactly “10 years”, but rather “10 years, 0 months, and 2 days”. This is upsetting, for obvious reasons!

Other various error-handling edge cases didn’t work, either.

Here are some examples of situations in which I didn’t like the output of the Google Sheets function, in the image below.

Output of Google Sheets human readable time period equation formula — Output of Google Sheets human readable time period equation

ChatGPT Prompt for the Human-Readable Time Period Function

Rather than write the function myself, I wrote the following function spec and gave it to ChatGPT.

Write a Google Sheets function “getTimePeriod(startDate, endDate)”.

Input:

startDate and endDate in Google Sheets format.
If the startDate is later than the endDate, the function should swap and compute the positive period.
Non-date inputs should return an error message: “Invalid date format”.

Output:

Calculate the period between the two dates and display it as “X years, Y months, and Z days”.
Doesn’t display zero values. E.g., not “0 years, 2 months and 2 days”, but rather just “2 months and 1 day”.
Corrects for plurals. E.g., not “1 years” but “1 year”.
Calculate periods that make intuitive sense even between months of different lengths. E.g. if a period is between the same day on two months, display the results as “x months exactly”, even if the months have different numbers of days in them.
When omitting the number of days, or months and days, because they have zero values, use the word “exactly”.
In cases like “2 years, and 15 days”, omit the trailing comma.
For invalid inputs, return: “Invalid Date Input”.

Logic:

Consider months with varying numbers of days.
Leap years should be factored into the calculation.

Examples:

getTimePeriod(“2 august 2023”, “2 august 2020”) should return “3 years exactly”.
getTimePeriod(“28 February 2020”, “28 June 2020”) should return “4 months exactly”.
getTimePeriod(A1, A2), where A1 and A2 contain Google Sheets date values, A1 contains the date 3 March 2022 and A2 contains the date 3 April 2023, should return “One year and one month exactly.”
getTimePeriod(A1, A2), where A1 contains 1 March 2020 and A2 contains 2 March 2023, should return “3 years and 1 day.”

Google Sheets Function for Human-Readable Time Period

The resulting Google Sheets function met the requirements and performed as expected.

function getTimePeriod(startDate, endDate) {
  try {
    // Convert inputs to Date objects
    let start = new Date(startDate);
    let end = new Date(endDate);

    // Swap if start date is after end date
    if (start > end) {
      [start, end] = [end, start];
    }

    let years = end.getFullYear() - start.getFullYear();
    let months = end.getMonth() - start.getMonth();
    let days = end.getDate() - start.getDate();

    // Correct for negative days or months
    if (days < 0) {
      days += new Date(end.getFullYear(), end.getMonth(), 0).getDate();
      months--;
    }
    if (months < 0) {
      months += 12;
      years--;
    }

    // Formatting the output string
    let result = [];

    if (years > 0) {
      result.push(years + (years === 1 ? " year" : " years"));
    }

    if (months > 0) {
      result.push(months + (months === 1 ? " month" : " months"));
    }

    if (days > 0) {
      result.push(days + (days === 1 ? " day" : " days"));
    }

    if (result.length === 1) {
      return result[0] + " exactly";
    } else if (result.length === 2) {
      return result.join(" and ");
    } else if (result.length === 3) {
      return result[0] + ", " + result[1] + ", and " + result[2];
    } else {
      throw new Error();
    }
  } catch (error) {
    return "Invalid Date Input";
  }
}

Below is some testing of both the formula and the function. The formula is “fine” but it’s always a bit off. The function has much more pleasant output.