Date Question using months as integers

[nathaniel_pulsi]

I’m trying to get the number of months remaining between an end date and today, as a whole integer. The specific use case is for monthly payments that occur during a month. By way of example, today is July 25th, and my end date is October 1, and the result i’d like to see is 4, eg, July, August, September, October.
i am close using this formula, but it’s not correct, as it’s displaying the daycount difference as a # of months. To use my example above, i’d get a result of 3, or possibly even 2.

DATETIME_DIFF(LastDate, TODAY(), ‘months’)

Is there a way to show LastDate as a month# and today as a month # and then the difference between them, even when last date may be 1-2 years in future? eg, if last date is July 1 2020, and today is July 25th, i need the result to be 13, and not 0 (7-7=0)

Any help is appreciated

[nathaniel_pulsi]

So i kept experimenting and have another issue- i converted today to a col called ThisMonth using DATETIME_FORMAT(TODAY(), ‘MM-YYYY’) and get the desired display of 07-2019, I also created a column called LastMMYY for my last date in same format, eg, 01-2021

Then i did a col called Mths with DATETIME_DIFF(LastMMYY, ThisMonth,‘m’) but surprisingly, all i get are errors. I investigated and airtable is not recognizing the two col using datetime_format as a date.

@Airtable, can you assist why DATETIME_FORMAT renders values not recognized as date fields?

[JonathanBowen]

Hi @nathaniel_pulsifer - the problem here is that when the DATETIME_DIFF() formula is used with a ‘months’ unit gives an answer of the number of whole months:

So, a difference of, say 3 months and 30 days evaluates to 3.

From your post it seems you want the number of months that a date range “touches”, so 31st July to 1st October covers, July, Aug, Sep and Oct, i.e. 4 months.

To get to what you want you need to a bit of date manipulation. Get the 1st of the month of your start date and the 1st of the month after your end date. You can then take these two dates away with DATETIME_DIFF.

1st of Month Start is:

DATETIME_FORMAT(Today, 'MM/01/YYYY')

1st of next month (end date) is:

DATETIME_FORMAT(DATEADD({End Date}, 1, 'months'), 'MM/01/YYYY')

And the formula is:

DATETIME_DIFF({1st of next month (end date)}, {1st of Month Start}, 'months')

To your last point, DATETIME_FORMAT takes a date field and manipulates it to a specific format, but the output is a string, hence the reason why you cannot format this in the field settings (although DATETIME_DIFF seems not to worry about this and accepts these strings as dates - presumably some conversion taking place within the DATETIME_DIFF function if the format validates as a date in some way).

JB

[Justin_Barrett]

Here’s what I came up with:

(MONTH({End Date}) + (YEAR({End Date}) - YEAR(TODAY())) * 12) - MONTH(TODAY()) + 1

[nathaniel_pulsi]

@Justin_Barrett and @JonathanBowen
Thank you guys so much- you both broke thru this logjam for me. Here’s what I did.
The use case here is that i’m trying to sum the cash balance of a series of escrow accounts that are set to make payments out on a variety of days within a month, eg, 24 monthly payments of $1000 from 5/17/2018 to 4/17/2020. So today, 7/26/19, the 7/17 payment has already been made, but the august 2019 thru april 2020 payment has not, ergo, i should have $10,000 remaining in the account for 10 payments still to be made. Had i done the report on 7/15, however, there should be $11,000 in the account.

to do this i needed to refine @Justin_Barrett formula instead of +1, to account for where we are in the month today vs day of the month when the payment had been made. I would need to count July for payments due on the 27th thru the 31’st, but not count July for payments previously in the month. So using @JonathanBowen solution, i got an integer of '1; for records that have not paid yet this month by extracting just the day of the month the payments are due compared to today’s day of the month in a Col i called ‘DueThisMth’
IF(DATETIME_FORMAT({From Date}, ‘DD’) > DATETIME_FORMAT(TODAY(), ‘DD’),1)
Then added this to Justin’s elegant solution. The end result is:

(MONTH(LastEscrowDate) + (YEAR(LastEscrowDate) - YEAR(TODAY())) * 12) - MONTH(TODAY()) + DueThisMth 

then I wrapped in some if’s to clean up results and it works perfectly. The only issue will be for payments that occur on the 29th, 30th or 31’st when looking at it in months that end either 28th or 30th, but i can live with that.

thank you!