Count the months between 2 dates

Questions & Answers
1

Hi Everyone,

I have a record in Airtable that has a [start date] and an [end date] these can span over several months. Lets take an example:

[Start date] = 01/08/2023
[End date] = 01/10/2023

I want to be able to take that record and split it into 1 record for each month.

  • Record 1 will be August
  • Record 2 will be September
  • Record 3 will be October

To do this I think I need to first determine how many months the [Start date] and [End date ] span (3 in this case).

I cant figure out how to do this. If I can extract the number of months then I can do the rest but this is proving more difficult than I thought.

Any ideas on how to tackle this please?

2

One way to tackle this, maybe, is to start with the end year, multiply by 12, then add the end month number.
From that, subtract the start year multiplied by 12, then add the start month.
Then, add 1 to that result.

From your example that’d be (202312+10) - (202312+8) + 1 = 24,286 - 24,284 + 1 = 3

Then use a repeater, starting at 0 and repeat the number of times from the previous answer, 3.
From each bundle of the repeater, which in this case is going to be 0, 1, 2, start with your start date and add i months.
August 2023 + 0 Months = August 2023
August 2023 + 1 Month = September 2023
August 2023 + 2 Months = October 2023

Here’s a blueprint you can check out.
monthsdifference.json (8.7 KB)

I’m curious how others handle this because I don’t think there are native ways to calculate date differences so this is interesting!

4 Likes
3

Thanks Donald, that will work. It is strange that there isn’t an easier way to do it. Curious too to see other ways to tackle it but much appreciated and thanks for going the extra mile with the blueprint.

1 Like
4

Turns out it is this complicated. Check out this link Calculate Duration between two Dates in Integroma - Techflow AI

3 Likes
5

I did see that and thought there must be an easier way :slight_smile: I think what you provided will work, just testing with my inputs but 3 out of 3 so far.

6

How about this for a simpler option:

{{formatDate(2.end; “M”) - formatDate(1.start; “M”) + 1}}

Extracts the month number from each date, you can then subtract one from the other and add one to get the total number of months spanned.

2 Likes
7

The problem, I think, is if the end date is something like Jan 2025 and the start date is Aug 2023 then it’d be 1 - 8 = -7.
-7 doesn’t correctly represent a 17-month span.

1 Like
8

Ah yes, sorry trying to over simplify!