Brian Dunning's FileMaker Custom Functions

alterDate ( entryDate ; m ; d ; y )

Can be used to change any or all of the month, day and/or year by adding/subtracting simple values, calculations, number/calc fields, or by substitution.

  Average rating: 3.9 (34 votes) Log in to vote

John Knight   John Knight
dB SPL
http://www.filemaker.com

Share on Facebook Share on Twitter

  Sample input:
alterDate ( "8/9/2010" ; 1 ; Quote(0) ; 10 )
  Sample output:
8/31/2020

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

Adds the values in 'm', 'd' & 'y' to the month, day and year of 'entryDate'.
entryDate can be anything that produces a valid date (e.g. a Date field, the Date() function, Get(CurrentDate), or a date entered as text).
m, d & y can be anything that produces a number (e.g. a Number field, a number, any calculation that produces a number).
m, d & y can also be wrapped by Quote(). Doing so replaces the respective value from entryDate with that number (e.g. alterDate ( "8/9/2010" ; 1 ; Quote(0) ; 0 ) produces 9/0/2010, which evaluates to 8/31/2010.
m, d & y can be positive or negative.

Examples: (assume DateField and Get(CurrentDate) = 8/9/2010)
alterDate ( DateField ; 0 ; Quote(1) ; 0 ) = 8/1/2010 (The first of the month)
alterDate ( DateField ; 0 ; 60 ; 0 ) = 10/8/2010 (60 days out)
alterDate ( Get ( CurrentDate ) ; 0 ; -DayOfWeek ( Get ( CurrentDate ) ) ; 0 ) = ( 8 ; 9-2 ; 2010 ) = 8/7/10 (The Saturday before)
alterDate ( DateField ; GetValue ( List ( 3 ; 6 ; 9 ; 12 ) ; 3 ) ; Mod ( 12 ; 7 ) ; Quote(1999) ) = ( 8+9 ; 9+5 ; 1999 ) = 17/14/1999 = 5/14/2000
alterDate ( DateField ; 2*3 ; 4^2 ; -12 ) = ( 8+6 ; 9+16 ; 2010-12 ) = 14/25/1998 = 2/25/1999

 

Comments

ron   ron, ID
Feb 11, 2015
Although this works for a single calculation, when placed in a loop, it becomes inaccurate after several loops. Why? I don't know. Seems like it shouldn't make any difference.
 
John Knight   John Knight, FL
Feb 12, 2015
You're right, being in a loop should have no effect since each call would be separate.

Do you have example code?
 

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 15 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:

Under construction. Email me your wish list for improvements.