MonthsBetween ( InDate1 ; InDate2 )

Calculate the months between two given dates (non-recursive)

Be the first to rate this function Log in to vote

Richard DeShong - Show more from this author
Logic Tools
http://logictools.com

Sample input:

MonthsBetween( 5/8/2020 ; 12/20/2021 )

Sample output:

19.3871

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

Case(
   // test for empty dates, and if both the same date:
   IsEmpty( InDate1 )  or  InDate1 = "Empty" ; 0 ;   // empty tests could be changed to error msg
   IsEmpty( InDate2 )  or  InDate2 = "Empty" ; 0 ;
   InDate1 = InDate2 ; 0 ;

   Let([
      D1 = Case( InDate1 < InDate2 ; InDate1 ; InDate2 ) ;   // make sure date1 is before date2
      D2 = Case( InDate1 < InDate2 ; InDate2 ; InDate1 ) ;
   
      Da1 = Day( D1 ) ; Mo1 = Month( D1 ) ; Yr1 = Year( D1 ) ;   // Day, Month, Year of each date
      Da2 = Day( D2 ) ; Mo2 = Month( D2 ) ; Yr2 = Year( D2 ) ;
      Mo1Days = Day( Date( Mo1 + 1 ; 1 ; Yr1 ) - 1 ) ;   // days in Month of each date
      Mo2Days = Day( Date( Mo2 + 1 ; 1 ; Yr2 ) - 1 )
   
   ];
      Case(
         // both within same month is simple calc:
         Yr1 = Yr2  and  Mo1 = Mo2 ; ( Da2 - Da1 ) / Mo1Days ;
   
         // Not the same month, so do a more complicated calc:
         Let([
            Da1Months = ( 1 - (  Da1 / Mo1Days ) ) ;   // percent of days left in month1
            Da2Months = ( Da2 / Mo2Days ) ;   // percent of days used in month2

            Mo2calc = Mo2 + Case( Mo2 < Mo1 ; 12 ; ( Yr1 + 1 ) = Yr2 ; 12 ) ;
            MoBetween = ( Mo2Calc - Mo1 - 1 ) ;
            MoMonths = Case( MoBetween < 1 ; 0 ; MoBetween ) ;
   
            YrBetween = ( Yr2 - Yr1 - 1 ) ;
            YrMonths = Case( YrBetween < 1 ; 0 ; YrBetween * 12 )
         ];

            ( YrMonths + MoMonths + Da1Months + Da2Months )

         ) // endlet  complicated calc
      ) // endcase   simple or complicated calc
   ) // endlet   parse out date values
) // endcase   empty dates and same date
/*
   Calculate months between 2 given dates (InDate1 and InDate2).

   Uses the actual number of months between the two dates, and then
   adds a percent of month for actual number of days extra days.
*/

Calculate months between 2 given dates (InDate1 and InDate2).
Uses the actual number of months between the two dates.
Does NOT use an Average-Days-Per-Month type calculation.
Uses the actual number of days in the two months of each date,
to calculate a percent remaining and used.
And then adds the number of months between the two dates.

Comments

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

MonthsBetween ( InDate1 ; InDate2 )

Comments

Support this website.

Support this website.