Usage of MONTHS_BETWEEN Function
Syntax
MONTHS_BETWEEN(date_field1, date_field2)
--Description: Returns the number of months between two dates, if less than one month, calculates decimal value by days, only allows date type fields
Example:
MONTHS_BETWEEN([Resignation Date], [Entry Date]), returns the number of months between "Resignation Date" and "Entry Date" on the same row
Applicable Scenarios
Scenario 1: Calculate the month difference between last month's end and date field
【Step 1】Create a new field to calculate last month's end: last_day(add_months(now(),-1))
Result as follows:
【Step 2】Use MONTHS_BETWEEN function to calculate month difference: MONTHS_BETWEEN([Last Day of Last Month],[Date])
Result as follows:
You can also nest the two functions together: MONTHS_BETWEEN( last_day(add_months(now(),-1)) ,[Date])
Scenario 2: Date and month-end alignment issues
① If this month has 31 days, compared with last month, then the month difference for the 31st of last month is 1, and the 30th will have decimal points;
② If this month has 30 days, compared with last month, then the month difference for the 30th and 31st of last month are the same, both are 1;
③ If February has 28 days, compared with last month, then the month difference for January 28th and January 31st are both 1, January 29th and January 30th will have decimal points:
Notes
- MONTHS_BETWEEN([Date1], [Date2]), if Date1 is greater than Date2, returns a positive number, otherwise, a negative number:
- If Date1 and Date2 are the last day of their respective months, or the same day, then the return will be an integer, otherwise the difference is calculated based on 31 days per month:
