NextDateRange ( StartDate ; TimePeriod ; WholePeriod ; FindPrevious )

Generate Date Ranges for Creating Daily, Weekly, Monthly, or Yearly Reports

Be the first to rate this function Log in to vote

Kyle Williams - Show more from this author
Augusto Digital
https://www.youtube.com/channel/UCpJlB7cmVNMjbjgY96MEsng

Sample input:

NextDateRange ( "6/29/2019" ; "m" ; 0 ; 1 )

Sample output:

5/30/2019
6/29/2019

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

/*
Parameters used:   StartDate, TimePeriod, WholePeriod, FindPrevious

Author:   Kyle Williams
Created:  6/26/2019
Purpose:  Creating Reports, returns a return delimited list of Start and End Dates for the next reporting line item:
NOTE:      FindPrevious is boolean 1 or 0
NOTE:      WholePeriod is boolean, if = 1, then it will find the next or previous whole week, month, or year. 
                - When Applied to "Day" then it will give the whole range in return delimited list.
*/

Let([
//StartDate = Get(CurrentDate);
//TimePeriod = "d";
//WholePeriod = GetAsBoolean ( 0 );  //Whole Week, Month or Year
//FindPrevious = GetAsBoolean ( 0 );


//FindPrevious = GetAsBoolean ( FindPrevious );
TimePeriod = Lower( TimePeriod );
TimePeriod = Case( TimePeriod = "d" or TimePeriod = "dy" or TimePeriod = "daily"     ; "day";
				TimePeriod = "w" or TimePeriod = "wk" or TimePeriod = "weekly"    ; "week";
				TimePeriod = "m" or TimePeriod = "mth" or TimePeriod = "monthly" ; "month";
				TimePeriod = "y" or TimePeriod = "yr" or TimePeriod = "yearly"      ; "year";
				TimePeriod );
d = Day ( StartDate );
m = Month ( StartDate );
y = Year ( StartDate )
];
Case ( 
	TimePeriod = "day"; 
		If( WholePeriod;
			If( FindPrevious;
					Date ( m ; d-1 ; y ) & ¶ & StartDate;  // Whole Day Range Previous
					StartDate & ¶ & Date ( m ; d+1 ; y )); // Whole Day Range Next
			If( FindPrevious ; Date ( m ; d-1 ; y ); Date ( m ; d+1 ; y ))
);
	TimePeriod = "week"; 
		If( WholePeriod;
			If( FindPrevious;
					Date ( m ; d-7 ; y ) & ¶ & Date ( m ; d-1 ; y );  //Whole Week Previous (Incomplete)
					Date ( m ; d+7 ; y ) & ¶ & Date ( m ; d+14 ; y )  //Whole Week Next (Incomplete)
			   );
			If( FindPrevious;
					Date ( m ; d-6 ; y ) & ¶ & Date ( m ; d ; y );  //Week Previous
					Date ( m ; d+1 ; y ) & ¶ & Date ( m ; d+7 ; y )  //Week Next
			  )
);
	TimePeriod = "month"; 
		If( WholePeriod;
			If( FindPrevious;
					Date ( m-1 ; 1 ; y ) & ¶ & Date ( m ; 0 ; y );     //Whole Month Previous
					Date ( m+1 ; 1 ; y ) & ¶ & Date ( m+2 ; 0 ; y )    //Whole Month Next
			   );
			If( FindPrevious;
					Date ( m-1 ; d+1 ; y ) & ¶ & Date ( m ; d ; y ); //Month Previous
					Date ( m ; d+1 ; y ) & ¶ & Date ( m+1 ; d ; y )    //Month Next
			  )
);

	TimePeriod = "year"; 
		If( WholePeriod;
			If( FindPrevious;
					Date ( 1 ; 1 ; y-1 ) & ¶ & Date ( 1 ; 0 ; y );     //Whole Year Previous
					Date ( 1 ; 1 ; y+1 ) & ¶ & Date ( 1 ; 0 ; y+2 )    //Whole Year Next
			   );
			If( FindPrevious;
					Date ( m ; d+1; y-1 ) & ¶ & Date ( m ; d ; y );   //Year Previous
					Date ( m ; d+1 ; y) & ¶ & Date ( m ; d; y+1 )     //Year Next
			   )

		   )
     )
)

When creating reports, date ranges can be a little challenging. Using this custom function, I am able to easily generate the start and end dates for the next line item in the report. This can be set up to find the first and last dates of every month by entering a 1 for "WholePeriod" otherwise it will use the current date. Depending on the direction of the report you can get the range for the next period or the previous period.

Comments

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

NextDateRange ( StartDate ; TimePeriod ; WholePeriod ; FindPrevious )

Comments

Support this website.

Support this website.