DateRange ( Range )

Creates a searchable date range to populate any date field while in Find Mode

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:

_DateRange ("Week" )
_DateRange ("Month" )
_DateRange ( 90 )

Sample output:

1/26/2020...2/1/2020 //Find last whole week (Sun-Sat)
1/1/2020...1/31/2020 //Find last whole month
11/6/2019...2/3/2020 //Find last 90 days

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

//Dependent on my IsNumeric function:       https://www.briandunning.com/cf/2299
//Also dependent on the SuperTrim function: https://www.briandunning.com/cf/904

Let([
cdt = Get(CurrentDate );
RangeNum = GetAsNumber(Range);
Range = Supertrim(Upper(Range))
];

Case(
	isNumeric(Range); cdt - (Range) & "..." & cdt - 1;

	Range = "WEEK"  or Range = "WK" or Range = "WK."  or Range = "W";

// LAST FULL WEEK: (Sunday thru Saturday)
Let(cdt = Get(CurrentDate );cdt - DayOfWeek ( cdt ) - 6 & "..." & cdt - DayOfWeek ( cdt )) ;

	Range = "MONTH" or Range = "MNTH"  or Range = "MTH"  or Range = "M"  ; 

Let([dt=Get(CurrentDate);m = Month ( dt ); d = Day ( dt ); y = Year ( dt )];     Date ( m-1 ; 1 ; y ) & "..." & Date ( m ; 0 ; y ));

	Range = "YEAR" or Range = "YR" or Range = "YR."  or Range = "Y" ;

Let([dt=Get(CurrentDate);m = Month ( dt ); d = Day ( dt ); y = Year ( dt )];   Date ( 1 ; 1 ; y-1 ) & "..." & Date ( 12 ; 31 ; y-1 )
)
)
)

When creating Reports a date range is often required. It is always nice to give the user multiple options for their reports. With this function it will automatically return a date range for any given number (up until yesterday) or you can specify you want a date range for last Week, Month or Year.

Comments

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

DateRange ( Range )

Comments

Support this website.

Support this website.