TextToDateRangeƒ ( text )

A rewrite of MagicDates by Andy Frazier with less code

Average rating: 4.1 (41 votes) Log in to vote

Michael Rhodes
Allied Data Service
http://allieddataservice.com

Sample input:

TextToDateRangeƒ ( "Next Quarter" )

Sample output:

"4/1/2009...7/31/2009"

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

/*
About
   Converts a text choice (such as from a drop down list) to a date range formatted for searches.
   This is an easier-to-read rework of Andy Frazier's MagicDates. Thank you, Andy, for your amazing custom function.
 
 Re-Authored by Michael Rhodes
 mike@allieddataservice.com

Parameters
   text must be one of the following:
      "Today"
      "Yesterday"
      "Tomorrow"
      ["This", "Last" or "Next"] & " Week"
      ["This", "Last" or "Next"] & " Month"
      ["This", "Last" or "Next"] & " Quarter"
      ["This", "Last" or "Next"] & " Year"
      ["This" or "Last"] & " YTD"

Example
   If current date is 3/30/2009, then
   TextToDateRangeƒ ( "Last Quarter" ) = "9/30/2008...12/31/2008"
*/

Let ( [
	_date = Get ( CurrentDate ) ;
	_month = Month ( _date ) ;
	_year = Year ( _date ) ;
	_day = DayOfWeek ( _date ) ;
	_span = RightWords ( _option ; 1 ) ;
	_dir = ( LeftWords ( _option ; 1 ) = "Next" ) - ( LeftWords ( _option ; 1 ) = "Last" )
] ;
	Case (
		_span = "Today" ;
		_date ;

		_span = "Yesterday" ;
		_date - 1 ;

		_span = "Tomorrow" ;
		_date + 1 ;

		_span = "Week" ;
		_date - _day + 1 + 7 * _dir & "..." & _date - _day+ 7 * ( 1 + _dir ) ;

		_span = "Month" ;
		Date ( _month + _dir ; 1 ; _year ) & "..." & Date ( _month + 1 + _dir ; 0 ; _year) ;

		_span = "Quarter" ;
		Date ( ( Div ( _month - 1 ; 3 ) + _dir ) * 3 + 1 ; 1 ; _year ) & "..." & Date ( ( Div ( _month - 1 ; 3 ) + _dir ) * 3 + 4 ; 0 ; _year ) ;

		_span = "Year" ;
		Date ( 1 ; 1 ; _year + _dir ) & "..." & Date ( 12 ; 31 ; _year + _dir ) ;

		_span = "YTD" ;
		Date ( 1 ; 1 ; _year + _dir ) & "..." & _date
	 )
)

See custom function MagicDates

Comments

		Shai Ben Mordechai, Tel Aviv Apr 3, 2009
How do you convert the date range from American to European date format ?

		Lee Smith Apr 5, 2009
Your quarters are not quarters. Second quarter is 4/1/2009 to 6/30/2009 Lee

		Michael Rhodes, Rancho Cucamonga Apr 13, 2009
Lee, "Second quarter" is not an option. Read the notes in the function. However, "This quarter" or "Last quarter" or "Next quarter" work just fine. The first three hours this was posted, there was a typo, so recopy the code if necessary.

		Michael Rhodes, Rancho Cucamonga Apr 13, 2009
Shai, I do not own a European version of FileMaker, so I can't test how FileMaker might use different parameters for it's date function.

		Shai Ben Mordechai, Tel Aviv Apr 18, 2009
Michael, You don't need to have a European version of FM. The date format belong to the "Regional and Language Options" in the control panel of the computer. In my case the date format is DD/MM/YYYY TIA Shai

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

TextToDateRangeƒ ( text )

Comments

Support this website.

Support this website.