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!

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

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

Test it yourself, and if you have any updates or suggestions, I’d love to know.

## Problems

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.