Newest functions Upload a function Leave a tip My account

There are 2,572 functions in this library.

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 dB SPL http://www.filemaker.com

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

// Created by Kn1ght 2010 Let ( [ numSigns = "0123456789-+" ; filterMonth = Filter ( m ; numSigns ) ; filterDay = Filter ( d ; numSigns ) ; filterYear = Filter ( y ; numSigns ) ] ; If ( not IsEmpty ( entryDate ) ; Date ( filterMonth + If ( Exact ( m ; filterMonth ) ; Month ( entryDate ) ) ; filterDay + If ( Exact ( d ; filterDay ) ; Day ( entryDate ) ) ; filterYear + If ( Exact ( y ; filterYear ) ; Year ( entryDate ) ) ) ) ) /* 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 */ Click to copy

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