DatePeriod ( Input ; Type )

DatePeriod week, month, quarter, year

Be the first to rate this function Log in to vote

Benjamin Fehr - Show more from this author
EfficientBizz GmbH
http://www.efficientbizz.com

Sample input:

DatePeriod ( "24.9.2019" ; "QQ" )
DatePeriod ( "24.9.2019" ; "MM" )

Sample output:

01.07.2019
30.09.2019
3
2019

01.09.2019
30.09.2019
9
2019

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

/*
DatePeriod

Input = Date
Type = WW (Week), MM (Month), QQ (Quarter), YY (Year)
Resultat = 	Date Begin of selected period  & ¶ &
			Date End of selected period & ¶ &
			Number of WeekOfYear (WW), MonthOfYear (YY), Quarter (QQ), '-' (YY) & ¶ &
			Year
Date are in European format as DD.MM.YYYY

Benjamin Fehr, EfficientBizz GmbH
 http://www.efficientbizz.com 
*/

Case ( 
	Type = "WW" ; 
		Let ( 
		[ 
			_Input = Input ; 
			_day = DayOfWeek ( _Input )-1 ;
			_week = WeekOfYear ( Input ) ;
			_year = Year ( Input ) ;
			start =  _Input - _day +1 ;
			end = start + 6  ;
			var2 = ""
		]
		; List (start ; end; _week; _year )
		) ;

	Type = "MM" ; 
		Let ( 
		[ 
			_Input = Input ; 
			_day = 1 ;
			_month = Month ( _Input ); 
			_month_end = Month ( _Input ) + 1 ;
			_year = Year ( _Input ) ;
			start =  Date ( _month ; _day ; _year ) ;
			end = Date ( _month_end ; _day ; _year ) - 1  ;
			var2 = ""
		]
		; List ( start  ;  end ; _month ; _year)
		) ;

	Type = "QQ" ; 
		Let ( 
		[ 
			_Input = Input ; 
			_day = 1 ;
			_month = Month ( _Input ); 
			_quarter = Ceiling ( _month / 3) ;
			_month_end = (_quarter * 3) +1 ;
			_year = Year ( _Input ) ;
			start =  Date ( _month_end - 3 ; _day ; _year ) ;
			end = Date ( _month_end ; _day ; _year ) -1  ;
			var2 = ""
		]
		; List ( start ; end; _quarter ; _year) 
		) ;

	Type = "YY" ; 
		Let ( 
		[ 
			_Input = Input ; 
			_day = 1 ;
			_month = 1; 
			_year = Year ( _Input ) ;
			_year_end = _year + 1 ;
			start =  Date ( _month ; _day ; _year ) ;
			end = Date ( _month ; _day ; _year_end ) - 1  ;
			var2 = ""
		] 
		; List ( start  ;  end; "-" ; _year )
		)

 )

Input = Date
Type = WW (Week), MM (Month), QQ (Quarter), YY (Year)
Resultat = Date Begin of selected period & ¶ &
Date End of selected period & ¶ &
Number of WeekOfYear (WW), MonthOfYear (YY), Quarter (QQ), '-' (YY) & ¶ &
Year
Date are in European format as DD.MM.YYYY
Week starts Monday and ends Sunday

Comments

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

DatePeriod ( Input ; Type )

Comments

Support this website.

Support this website.