MonthYearList2 ( StartDate ; EndDate ; ExcludeDates ; display )

Month Year List from date range (start and end date)

Be the first to rate this function Log in to vote

Alex Bassin - Show more from this author
SYNAP
http://www.synap.net

Sample input:

MonthYearList ( "1/2/2018"; 5/5/2019; 3/4/2019 3 )

Sample output:

1/2018
2/2018
3/2018
4/2018
5/2018
6/2018
7/2018
8/2018
9/2018
10/2018
11/2018
12/2018
1/2019
2/2019
4/2019

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

//MonthYearList2 ( StartDate; EndDate; ExcludeDates; display )
/*
- returns a return-delimited list of month and years starting based on a date range
Parameters
- short: 1= Full month name, 2= short month name, 3= number of month / year, 4=as date (first of the month)
Credit: Alex Bassin, SYNAP Corporation - www.synap.net
Besure to format the excluded dates the same way as the display format here. You can use the DateDisplay function for that

-Multiple Exclude Dates can added by separating the dates with a ¶ or comma. You must use The DateDisplay Function to display dates properly)
*/
Let ( [

//TEST
/*
StartDate="1/1/2018";
EndDate= "5/1/2023";
display=1;
ExcludeDates=DateDisplay ("1/1/2020¶2/12/2020"; display;"");
*/

//-----------------------
//Check for no End Period
StartDate = If ( StartDate=""; GetAsDate ("1/1/1900") ; GetAsDate (StartDate));
CurrDate = Get ( CurrentDate );
EndDate = If ( EndDate=""; If (StartDate ≤ CurrDate; Date (Month(CurrDate)+1;0;Year(CurrDate)); GetAsDate ("12/31/2999")) ; GetAsDate (EndDate));
NumberofMonths = Abs(Round (GetAsNumber(EndDate -  StartDate  )/30 ;0));
StartMonth= Month ( StartDate );
StartYear= Year ( StartDate );
EndYear= Year ( EndDate );
//Exclude Dates
ExcludeDates= Substitute ( ExcludeDates ;[ "," ; "¶" ];[ ";" ; "¶" ]);
~year = If ( 
		StartMonth ≥ 13 ;
		If ( 
			PatternCount ( StartMonth / 12 ; "," ) > 0 ;
			StartYear + Int ( StartMonth / 12 ) ; 
			StartYear + ( Int ( StartMonth / 12 ) - 1 )
			) ;
		StartYear
		) ;
~mn = MonthName ( Date ( StartMonth ; "1" ; ~year ) ) & " " & ~year ;
Eval = Case ( 
		display = 2 ; Left ( ~mn ; 3 ) & " " & ~year ;
		display = 3 ; If ( Length(StartMonth)=1; "0" & StartMonth ; StartMonth ) & "/" & ~year ;
		display = 4 ; Date (StartMonth; 1;  ~year) ;

		 ~mn )
] ;

//Error Trapping
If (
	StartYear≤1900 or StartYear≥2900 or StartYear="" or EndYear≤1900 or EndYear≥2999 or EndDate<StartDate; ""
	;
//Perform Function
If ( FilterValues  (ExcludeDates ;  Eval)≠"" ; "" ; Eval & "¶")
 	& 

	If ( NumberofMonths ≥ 1 ; 
		MonthYearList2 ( 
			Date (StartMonth + 1 ; 1 ; StartYear);  
			EndDate ; 
			ExcludeDates ;
			display
			) 
		)
	)
)

- returns a return-delimited list of month and years starting based on a date range
Parameters
- short: 1= Full month name, 2= short month name, 3= number of month / year, 4=as date (first of the month)

5/10/2021 - added exclude dates feature

Credit: Alex Bassin, SYNAP Corporation - www.synap.net
Special thanks to David Head who build the original

Comments

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

MonthYearList2 ( StartDate ; EndDate ; ExcludeDates ; display )

Comments

Support this website.

Support this website.