DateMinusMonths ( theDate ; format ; months )

Function that calculates the date based on the number of months you specify, and based on the format that can be in.

Average rating: 4.0 (1 vote) Log in to vote

MarcNL - Show more from this author
https://www.linkedin.com/in/marc-brunsman-4468a287/

Sample input:

DateMinusMonths ( "14-05-2025" ; "mmddyyyy" ; 25 )

Sample output:

4-14-2023

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

// Function that calculates the date based on the number of months you specify, and based on the format that can be in.

// Note: Date that is entered must be in the format that Filemaker is set to on the system it is running on.
// DateMinusMonths ( "05-14-2025" ; format ; months )

// ddmmyyyy (DD-MM-YYYY)
// mmddyyyy (MM-DD-YYYY)
// yyyymmdd (YYYY-MM-DD)

Let (
    [ 
        ~date = theDate ;
        ~monthsBack = months ;
        ~format = format ;
        ~years = ~monthsBack / 12 ;
        ~fullYearCheck = ~years = Floor ( ~years ) ;
        ~monthFirst = Month ( ~date ) - ((~years - Floor ( ~years )) * 12) ;
        ~year = Year ( ~date ) - Floor ( ~years ) ;
        ~monthFinal= Case ( ~monthFirst <= 0 ; 12 + ~monthFirst ; ~monthFirst ) ;
        ~year = Case ( ~monthFirst <= 0 ; ~year - 1 ; ~year ) 
    ] ; 
        Case (
            ~format = "ddmmyyyy" ;
                Day ( ~date ) & "-" & ~monthFinal & "-" & ~year  ;
            ~format = "mmddyyyy" ;
                ~monthFinal & "-" & Day ( ~date )  & "-" & ~year ;
            ~format = "yyyymmdd" ;
                ~year & "-" & ~monthFinal & "-" & Day ( ~date )  ;
            "No correct format, has to be \"ddmmyyyy\", \"mmddyyyy\" or \"yyyymmdd\""
        )
)

This custom FileMaker function calculates a past date by subtracting a specified number of months from a given date. It is designed to return the result in a specific date format of your choice.

Parameters:
theDate – A date value (as text or a valid FileMaker date).
----------------
format – A text string indicating the desired output format. Accepted values are:
"ddmmyyyy" → returns day-month-year (e.g., 14-05-2025)
"mmddyyyy" → returns month-day-year (e.g., 05-14-2025)
"yyyymmdd" → returns year-month-day (e.g., 2025-05-14)
-----------------
months – A number indicating how many months to subtract from the original date.
______________
Behavior:
The function computes the total number of years and remaining months to subtract.
It adjusts the date accordingly, preserving the day of the month.
The result is returned as a formatted text string based on the specified format.
If an unsupported format string is used, a descriptive error message is returned.
______________
Important:
The input theDate must be in a format recognized by FileMaker on the system where the function runs.
The function does not convert free-text dates to FileMaker dates; it assumes the input is already correctly interpreted as a date.
______________
Limitations:
No handling for edge cases like subtracting from the 31st in a month with fewer days.
No format validation beyond checking the provided format parameter.

Comments

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

DateMinusMonths ( theDate ; format ; months )

Comments

Support this website.

Support this website.