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.

1. It doesn’t change plurals. For example “1 years” or “2 years”.
2. It shows zero answers. For example, if it’s less than a year, the result says “0 years”.
3. 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!

There were other various error-handling edge cases that didn’t wor, either.

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

## 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.”

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 + " exactly";
} else if (result.length === 2) {
return result.join(" and ");
} else if (result.length === 3) {
return result + ", " + result + ", and " + result;
} 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.

The only problem with the above code is that it doesn’t work if one date is `=now()`. But that’s not a problem for me.