IncrementDate ( StartDate ; Frequency )

Returns a date incrementally later than StartDate.

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

Kevin Bache
None
www.none.com

Sample input:

IncrementDate ( "7/31/07" ; "Monthly" )

IncrementDate ( "8/31/07" ; "Monthly" )

IncrementDate ( "11/29/07" ; "Semi-Monthly" )

IncrementDate ( Get ( CurrentDate ) ; "Quarterly" )

Sample output:

8/31/2007

9/30/2007

12/15/2007

2/7/2008

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

/* 
-------------------------
IncrementDate v1.1
-------------------------
By Kevin Bache

Returns a date incrimentally later than StartDate.

Accepted incriments are:
Weekly
Bi-Weekly
Semi-Monthly
Monthly
Bi-Monthly
Quarterly
Semi-Annually
Annually
Bi-Annually

If the output day is greater than the last day of the output month, it turns into the last day of the output month.
For instance, the Monthly increment from 5/31/07 will be 6/30/07, not 7/1/07 or 6/31/07.

Semi-Monthly is set up so that you can continue running it all year with a total of 24 run-dates.  Since different months have different numbers of days, this means that:
Semi-Monthly StartDates with day > 28 will increment to the 14th of the next month.
For instance, IncrementDate ( 3/31/07 ; Semi-Monthly ) = 4/14/07

Semi-Monthly StartDates with day < 15 will increment to StartDate + 14 of the current month..
For instance, IncrementDate ( 4/14/07 ; Semi-Monthly ) = 4/28/07

Semi-Monthly StartDates with 15 <= day < 29 will increment to StartDate - 14 of the next month.
For instance, IncrementDate ( 4/28/07 ; Semi-Monthly ) = 5/14/07

The rest work as you'd expect.

Email trendymoniker[_at_]gmail.com with bugs, questions, comments or feature requests.
*/

Let ( 
	[ 
	LastDayOfStartMonth = Day ( Date ( Month ( GetAsDate ( StartDate ) ) + 1 ; 0 ; Year ( GetAsDate ( StartDate ) ) ) );

	YearOut = Year ( GetAsDate ( StartDate)  ) ;

	MonthOut =
	Case	( 

		Frequency = "Weekly" ;
		Month ( GetAsDate ( StartDate ) ) ;

		Frequency = "Bi-Weekly" ;
		Month ( GetAsDate ( StartDate ) ) ;

		Frequency = "Semi-Monthly" ;
		If 	( 
			Day ( GetAsDate ( StartDate ) ) >= 15 ; 
			Month ( StartDate ) + 1 ;

			// Else
			Month ( StartDate )
			) ;

		Frequency = "Monthly" ;
		Month ( GetAsDate ( StartDate ) ) + 1 ;

		Frequency = "Bi-Monthly" ;
		Month ( GetAsDate ( StartDate ) ) + 2 ;

		Frequency = "Quarterly" ;
		Month ( GetAsDate ( StartDate ) ) + 3 ;

		Frequency = "Semi-Annually" ;
		Month ( GetAsDate ( StartDate ) ) + 6 ;

		Frequency = "Annually" ;
		Month ( GetAsDate ( StartDate ) ) + 12 ;

		Frequency = "Bi-Annually" ;
		Month ( GetAsDate ( StartDate ) ) + 24 ;

		) ; // Close Month Case

	LastDayOfMonthOut = Day ( Date ( MonthOut + 1 ; 0 ; YearOut ) ) ;

	DayOut =
	Case	( 
		Frequency = "Weekly" ;
		Day ( GetAsDate ( StartDate ) ) + 7 ;

		Frequency = "Bi-Weekly" ;
		Day ( GetAsDate ( StartDate ) ) + 14 ;

		Frequency = "Semi-Monthly" ;
		Case 	( 
			Day ( GetAsDate ( StartDate ) ) >= 29 ; 
			14;

			Day ( GetAsDate ( StartDate ) ) >= 15 and Day ( GetAsDate ( StartDate ) ) < 29 ; 
			Day ( GetAsDate ( StartDate ) ) - 14 ;

			// Else
			Day ( GetAsDate ( StartDate ) ) + 14
			) ;

		Frequency = "Monthly" ;
		If 	( 
			Day ( GetAsDate ( StartDate ) ) > LastDayOfMonthOut ;
			LastDayOfMonthOut ;
			
			// Else
			Day ( GetAsDate ( StartDate ) )
			) ;
			
		
		Frequency = "Bi-Monthly" ;
		If 	( 
			Day ( GetAsDate ( StartDate ) ) > LastDayOfMonthOut ;
			LastDayOfMonthOut ;
			
			// Else
			Day ( GetAsDate ( StartDate ) )
			) ;

		Frequency = "Quarterly" ;
		If 	( 
			Day ( GetAsDate ( StartDate ) ) > LastDayOfMonthOut ;
			LastDayOfMonthOut ;
			
			// Else
			Day ( GetAsDate ( StartDate ) )
			) ;

		Frequency = "Semi-Annually" ;
		If 	( 
			Day ( GetAsDate ( StartDate ) ) > LastDayOfMonthOut ;
			LastDayOfMonthOut ;
			
			// Else
			Day ( GetAsDate ( StartDate ) )
			) ;

		Frequency = "Annually" ;
		If 	( 
			Day ( GetAsDate ( StartDate ) ) > LastDayOfMonthOut ;
			LastDayOfMonthOut ;
			
			// Else
			Day ( GetAsDate ( StartDate ) )
			) ;

		Frequency = "Bi-Annually" ;
		If 	( 
			Day ( GetAsDate ( StartDate ) ) > LastDayOfMonthOut ;
			LastDayOfMonthOut ;
			
			// Else
			Day ( GetAsDate ( StartDate ) )
			) ;

		) // Close Day Case

	] ;

Date	(
		MonthOut ;
		DayOut ;
		YearOut
	)

//For Testing:
//MonthOut & " / " & DayOut & " / " & YearOut
//MonthOut
//DayOut
//YearOut

 ) // Close Let

Simple function that returns a date incrementally later than StartDate.

Accepted increments are:
Weekly
Bi-Weekly
Semi-Monthly
Monthly
Bi-Monthly
Quarterly
Semi-Annually
Annually
Bi-Annually

Comments

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

IncrementDate ( StartDate ; Frequency )

Comments

Support this website.

Support this website.