MagicDates ( range )

Gives you FM-Find Friendly date ranges based on User-Friendly input. Works great with the

Average rating: 4.3 (36 votes) Log in to vote

Andy Frazier - Show more from this author
Mx4Px
http://www.andyfrazier.net

Sample input:

MagicDates("LastQuarter")

Sample output:

4/1/2007...6/30/2007

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

Case(
range="Today";Get(CurrentDate);
range="Yesterday";Get(CurrentDate)-1;
range="Tomorrow";Get(CurrentDate)+1;
range="ThisWeek";Get(CurrentDate)-DayOfWeek(Get(CurrentDate))+1&"..."&Get(CurrentDate)+(7-DayOfWeek(Get(CurrentDate)));
range="LastWeek";Get(CurrentDate)-DayOfWeek(Get(CurrentDate))-6&"..."&Get(CurrentDate)-DayOfWeek(Get(CurrentDate));
range="NextWeek";Let(sunday=Get(CurrentDate)+(8-DayOfWeek(Get(CurrentDate)));sunday&"..."&sunday+7);
range="ThisMonth";Let(firstday=Date(Month(Get(CurrentDate));1;Year(Get(CurrentDate)));firstday&"..."&Date(Month(firstday)+1;0;Year(firstday)));
range="LastMonth";Let(lastday=Date(Month(Get(CurrentDate));0;Year(Get(CurrentDate)));
Date(Month(lastday);1;Year(lastday))&"..."&lastday);
range="NextMonth";Let(firstday=Date(Month(Get(CurrentDate))+1;0;Year(Get(CurrentDate)))+1;firstday&"..."&Date(Month(firstday)+1;0;Year(firstday)));
range="ThisYTD";Date(1;1;Year(Get(CurrentDate)))&"..."&Get(CurrentDate);
range="LastYTD";Date(1;1;Year(Get(CurrentDate))-1)&"..."&Date(Month(Get(CurrentDate));Day(Get(CurrentDate));Year(Get(CurrentDate))-1);
range="NextYTD";Date(1;1;Year(Get(CurrentDate))+1)&"..."&
Date(Month(Get(CurrentDate));Day(Get(CurrentDate));Year(Get(CurrentDate))+1);
range="ThisYear";Date(1;1;Year(Get(CurrentDate)))&"..."&Date(12;31;Year(Get(CurrentDate)));
range="LastYear";Date(1;1;Year(Get(CurrentDate))-1)&"..."&Date(12;31;Year(Get(CurrentDate))-1);
range="NextYear";Date(1;1;Year(Get(CurrentDate))+1)&"..."&Date(12;31;Year(Get(CurrentDate))+1);
range="ThisQuarter";Let ( xMod = Case ( Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) = 0 ; 3; Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) ) ;
Date ( Month ( Get ( CurrentDate ) ) - xMod + 1; 1 ; Year ( Get ( CurrentDate ) ) )  &"..."&Get(CurrentDate));
range="LastQuarter";Let ( xMod = Case ( Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) = 0 ; 3; Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) ) ;Date ( Month ( Get ( CurrentDate ) ) - xMod - 2 ; 1 ; Year ( Get ( CurrentDate ) ) )&"..."&Date ( Month ( Get ( CurrentDate ) ) - xMod + 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) - 1 );
range="NextQuarter";Let ( xMod = Case ( Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) = 0 ; 3; Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) ) ;Date ( Month ( Get ( CurrentDate ) ) - xMod + 4 ; 1 ; Year ( Get ( CurrentDate ) ) )&
"..."&Date ( Month ( Get ( CurrentDate ) ) - xMod + 7 ; 1 ; Year ( Get ( CurrentDate ) ) ) - 1 )
)

Available ranges:
Today
Yesterday
Tomorrow
ThisWeek
LastWeek
NextWeek
ThisMonth
LastMonth
NextMonth
ThisYTD
LastYTD
NextYTD
ThisYear
LastYear
NextYear
ThisQuarter
LastQuarter
NextQuarter

**Special thanks to Excelisys & the "Business Tracker" for the brilliantly simple way of calculating quarters.

Comments

		Hal, Aiken, SC Jun 10, 2010
Very handy function!

		Doug Larsen, Mpls, MN Sep 15, 2010
A very handy function but be aware that the parameter called ThisQuarter actually returns a date range that that would more accurately be called QuarterToDate. For instance, if it is 8/15/2010 and you run the function using parameter "ThisQuarter" you would likely expect it to return: 7/1/2010...9/30/2010 But it actually returns: 7/1/2010...8/15/2010 PS. I couldn't find an email address for the author so he is unaware of the problem.

		Lazarus Sismanis, Decision Group Jun 17, 2021
@Doug Larsen: Good observation, regarding 'ThisQuarter". For those that need help in addressing this, you can replace the (entire) line for: "range="ThisQuarter" " with the following lines to create selections for "ThisQuarter" and "ThisQuarterToDate" range="ThisQuarter"; Let ( xMod = Case ( Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) = 0 ; 3; Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) ) ; Date ( Month ( Get ( CurrentDate ) ) - xMod + 1; 1 ; Year ( Get ( CurrentDate ) ) ) &"..."& Date ( Month ( Get ( CurrentDate ) ) + 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) - 1); range="ThisQuarterToDate";Let ( xMod = Case ( Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) = 0 ; 3; Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) ) ; Date ( Month ( Get ( CurrentDate ) ) - xMod + 1; 1 ; Year ( Get ( CurrentDate ) ) ) &"..."&Get(CurrentDate));

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

MagicDates ( range )

Comments

Support this website.

Support this website.