Newest functions Upload a function Leave a tip My account

There are 2,572 functions in this library.

AddMonths

Properly adds months to dates

  Average rating: 5.0 (2 votes) Log in to vote

Ian Summerfield - Show more from this author

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/* Will add numberOfMonths to myDate; result is a date Why do you need this? Because adding a month to the 31st January is NOT the 3rd March as Filemaker would have it if you just add 1 to the month of Date(1;31;2019), in a payroll situation you' would expect to get your money on the last day of February instead, i.e. 28th or 29th February. This function takes care of it. */ Let( [ vMonth = Month( myDate); vDay = Day( myDate); vYear = Year( myDate); vInitialResult=Date(vMonth+numberOfMonths;vDay;vYear); vNewDay=Day(vInitialResult) ]; Case(vNewDay ≠vDay; Date(Month(vInitialResult);1;Year(vInitialResult))-1; vInitialResult) ) /* Our result is simply a case of seeing if the result day is the same as before, if not then filemaker has wrapped it around to the next month, we only need set the day back to 1 and deduct a day to get the last day of the previous month. It means the adding 14 months to Dec 30th 2018 results in 29th Feb 2020 */ Click to copy

You don't need this function! Please don't tell me you can simply add to the month using the date function! You can't. If you are working with Payrolls or something similar, you need this function.

I wanted to pay someone on the 30th of each month, but there is no 30 in February. Filemaker handles this by simply wrapping over in the next month, resulting in the 2nd March. Any normal company will pay on the last day on the month instead. This function deals with it, it'll return 28th Feb or 29th Feb as needed by simply seeing the day no longer matches after adding the required amounts of months and then reverting to 1st of that month and deducting a day, that gives the last day of the month, Jan 30 + 1 month = Mar 03; make that Mar 01 and deduct a day; result = Feb 28 or 29.

 

Comments

		Bruno Pelletier, Geniewave Mar 3, 2021
Thanks Ian, very useful function !

		Greg Hains, Divide By Zero Pty Ltd Mar 16, 2023
Despite it's simplicity, this really is very useful. Thankyou Ian.

		Craig List Apr 23, 2024
Extremely useful! Critical for calculating expiration dates for patents.

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 21 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: