RangeDateEnd ( aRange, aYear )

Calculates the end date for a range based on value list.

Average rating: 4.4 (35 votes) Log in to vote

Tim Cimbura - Show more from this author
LuminFire
http://luminfire.com

Sample input:

RangeDateEnd("January", 2008)

Sample output:

1/31/2008

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

Let ([$aRange=aRange;
$aYear=aYear];
Case(
$aRange="This Year";Date(12;31;Year(Get(CurrentDate)));
$aRange="Last Year";Date(12;31;Year(Get(CurrentDate))-1);
$aRange="Next Year";Date(12;31;Year(Get(CurrentDate))+1);

$aRange="This YTD";Get(CurrentDate);
$aRange="Last YTD";Date(Month(Get(CurrentDate));Day(Get(CurrentDate));Year(Get(CurrentDate))-1);
$aRange="Next YTD";Date(Month(Get(CurrentDate));Day(Get(CurrentDate));Year(Get(CurrentDate))+1);
$aRange ="Any YTD";Date(Month(Get(CurrentDate));Day(Get(CurrentDate));$aYear);

$aRange="This Quarter";Get(CurrentDate);
$aRange="Last Quarter";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 ) ) ) - 1 );
$aRange="Next Quarter";Let ( xMod = Case ( Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) = 0 ; 3; Mod ( Month ( Get ( CurrentDate ) ) ; 3 ) ) ;Date ( Month ( Get ( CurrentDate ) ) - xMod + 7 ; 1 ; Year ( Get ( CurrentDate ) ) ) - 1 );
$aRange="Quarter 1"; Date(3;31;$aYear);
$aRange="Quarter 2"; Date(6;30;$aYear);
$aRange="Quarter 3"; Date(9;30;$aYear);
$aRange="Quarter 4"; Date(12;31;$aYear);

$aRange="This Month";Let(firstday=Date(Month(Get(CurrentDate));1;Year(Get(CurrentDate)));Date(Month(firstday)+1;0;Year(firstday)));
$aRange="Last Month";Let(lastday=Date(Month(Get(CurrentDate));0;Year(Get(CurrentDate)));lastday);
$aRange="Next Month";Let(firstday=Date(Month(Get(CurrentDate))+1;0;Year(Get(CurrentDate)))+1;Date(Month(firstday)+1;0;Year(firstday)));
$aRange="January"; Date(1;31;$aYear);
$aRange="February"; Date(2; 28+Evaluate(Day(Date(2;29;$aYear))=29);$aYear); /* Accounts for leap years where Feb 29 may exist */
$aRange="March"; Date(3;31;$aYear);
$aRange="April"; Date(4;30;$aYear);
$aRange="May"; Date(5;31;$aYear);
$aRange="June"; Date(6;30;$aYear);
$aRange="July"; Date(7;31;$aYear);
$aRange="August"; Date(8;31;$aYear);
$aRange="September"; Date(9;30;$aYear);
$aRange="October"; Date(10;31;$aYear);
$aRange="November"; Date(11;30;$aYear);
$aRange="December"; Date(12;31;$aYear);

$aRange="This Week";Get(CurrentDate)+(7-DayOfWeek(Get(CurrentDate)));
$aRange="Last Week";Get(CurrentDate)-DayOfWeek(Get(CurrentDate));
$aRange="Next Week";Let(sunday=Get(CurrentDate)+(8-DayOfWeek(Get(CurrentDate)));sunday+6);

$aRange="Today";Get(CurrentDate);
$aRange="Yesterday";Get(CurrentDate)-1;
$aRange="Tomorrow";Get(CurrentDate)+1)
)

Use with RangeDateStart function. A global start and end date calculated with this function can be used to filter a portal, choose report dates, etc.

Note: The end of "This Quarter" is defined as the current date...assuming there are no date values beyond it.

Call with values from value list:
This Year
Last Year
Next Year
This YTD
Last YTD
Next YTD
This Quarter
Last Quarter
Next Quarter
Quarter 1
Quarter 2
Quarter 3
Quarter 4
This Month
Last Month
Next Month
January
February
March
April
May
June
July
August
September
October
November
December
This Week
Last Week
Next Week
Today
Yesterday
Tomorrow

Comments

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

RangeDateEnd ( aRange, aYear )

Comments

Support this website.

Support this website.