DateCalculation ( )

Function stats

Average user rating
360
334
9999
Support
FileMaker 10.0 +
Date posted
03 July 2012
Last updated
03 July 2012
Version
Recursive function
No

Author Info
 dawolfe

1 functions

Average Rating None

author_avatar



 

Function overview

Prototype

DateCalculation  ( From;   Years;   Months;   Days )


Parameters

From  


Years  


Months  


Days  


Description

Tags:  date   calculator   calculation  

This function calculates a target date from years, months and days values added to or subtracted from a source "from" date.

The values of Years, Months and Days can be any positive or negative number.

Ex: DateCalculation ( "6/30/2012" ; "-1" ; "3" ; "0" )

The above call would calculate 1 year back and 3 months forward from 6/30/2012 resulting in a return of 9/30/2011. Each component of the calculation is converted into a "days from" number and then added together to come up with target date. This will allow the function to cover leap years correctly rather than just removing a value from the year aspect of the source "from" value.

When calculating the months difference value the function will adjust for falling on invalid dates. Meaning, if the function calls DateCalculation ("4/30/2012" ; "0" ; "-2" ; "0" ) the target result would be "2/30/2012", an invalid date. The function will locate the closest date that falls before the target invalid date, in this case "2/29/2012" since 2012 was a leap year.

Examples

Sample input

DateCalculation ( "6/30/2012" ; "-1" ; "3" ; "0" )


Sample output

Will subtract 1 year and add three months to 6/30/2012 to return a result of 9/30/2011.

 

Function code

/*

This function calculates a target date from years, months and days values added to or subtracted from a source "from" date.

The values of Years, Months and Days can be any positive or negative number.

Ex: DateCalculation ( "6/30/2012" ; "-1" ; "3" ; "0" )

The above call would calculate 1 year back and 3 months forward from 6/30/2012 resulting in a return of 9/30/2011. Each
component of the calculation is converted into a "days from" number and then added together to come up with target date.
This will allow the function to cover leap years correctly rather than just removing a value from the year aspect of the
source "from" value.

When calculating the months difference value the function will adjust for falling on invalid dates. Meaning, if the function
calls DateCalculation ("4/30/2012" ; "0" ; "-2" ; "0" ) the target result would be "2/30/2012", an invalid date. The function
will locate the closest date that falls before the target invalid date, in this case "2/29/2012" since 2012 was a leap year.

*/


Let ( [

/* Number if years within month value */
t = If ( Abs ( Months ) > 12 ;
Int ( Months / 12 ) ;
0
)
;
/* Number of months remaining after years within month value are removed */
l = Case (
Months < 0 and t ≠ 0 ; 0 - ( Abs ( Months ) - Abs ( ( t ) * 12 ) ) ;
Months > 0 and t ≠ 0 ; Abs ( Months ) - Abs ( ( t ) * 12 ) ;
Months
)
;
/* Month value of From month plus remaining months after years within month value are removed */
k = Month ( From ) + l
;
/* Adjusted new month value if greater than 13 or less than 0 */
c = Case (
k > 13 ; k - 12 ;
k < 0 ; 12 + k ;
k = 0 ; 12 ;
k
)
;
/* Year value adjuster if remaining month values top or bottom out of range */
b = Case (
k > 13 ; 1 ;
k ≤ 0 ; -1 ;
0
)
;
/* Correct months target if falling on an invalid date */
e =GetValue ( Substitute (
GetAsDate ( c & "/" & Day ( From ) & "/" & ( Year ( From ) + t + b ) ) & "¶" &
GetAsDate ( c & "/" & Day ( From ) - 1 & "/" & ( Year ( From ) + t + b ) ) & "¶" &
GetAsDate ( c & "/" & Day ( From ) - 2 & "/" & ( Year ( From ) + t + b ) ) & "¶" &
GetAsDate ( c & "/" & Day ( From ) - 3 & "/" & ( Year ( From ) + t + b ) ) & "¶"
; "?¶" ; "" ) ; 1 )
;
y = Case (
Years > 0 ; GetAsDate ( Month ( From ) & "/" & Day ( From ) & "/" & Year ( From ) + Years ) - GetAsDate ( From ) ;
Years < 0 ; 0 - ( GetAsDate ( From ) - GetAsDate ( Month ( From ) & "/" & Day ( From ) & "/" & Year ( From ) + Years ) ) ;
0
)
;
m = Case (
Months > 0 ; GetAsDate ( e ) - GetAsDate ( From ) ;
Months < 0 ; 0 - ( GetAsDate ( From ) - GetAsDate ( e ) ) ;
0
)
;
d = Days
] ;

GetAsDate ( GetAsDate ( From ) + m + y + d )

/* Uncomment this block to debug */
/*
& "¶¶" &
"t=" & t & "¶" &
"l=" & l & "¶" &
"k=" & k & "¶" &
"c=" & c & "¶" &
"b=" & b & "¶" &
"y=" & y & "¶" &
"m=" & m & "¶" &
"d=" & d & "¶" &
"e=" & e & "¶" &
"month adjusted date=" & c & "/" & Day ( From ) & "/" & ( Year ( From ) + t + b ) & "¶" &
"all calculated=" & GetAsDate ( GetAsDate ( From ) + m + y + d )
*/

)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/334

    Prototype: DateCalculation( From; Years; Months; Days )
    Function Author: dawolfe (http://www.fmfunctions.com/mid/360)
    Last updated: 03 July 2012
    Version: 1.1

*/
// ===================================

 

Comments

Nick
10 December 2014



Hi - this is fantastic function - thanks so much. Has saved me hours of work.

Nick
  General comment

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (31)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Interface  (15)
Layout  (15)
Text  (14)
Variables  (12)
Layout Objects  (11)
Filter  (11)
Design  (10)
Array  (7)