RangeDateStart ( aRange, aYear )

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

Average rating: 4.6 (31 votes) Log in to vote

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

Sample input:

RangeDateStart("Last Year")

Sample output:

1/1/2006 (Assuming the current year is 2007)

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

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

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

$aRange ="This 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 ) ) ) );
$aRange ="Last Quarter";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 ) ) ) );
$aRange ="Next Quarter";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 ) ) ) );
$aRange="Quarter 1"; Date(1;1;$aYear);
$aRange="Quarter 2"; Date(4;1;$aYear);
$aRange="Quarter 3"; Date(7;1;$aYear);
$aRange="Quarter 4"; Date(10;1;$aYear);

$aRange ="This Month";Let(firstday=Date(Month(Get(CurrentDate));1;Year(Get(CurrentDate)));firstday);
$aRange ="Last Month";Let(lastday=Date(Month(Get(CurrentDate));0;Year(Get(CurrentDate))); Date(Month(lastday);1;Year(lastday)));
$aRange ="Next Month";Let(firstday=Date(Month(Get(CurrentDate))+1;0;Year(Get(CurrentDate)))+1;firstday);
$aRange="January"; Date(1;1;$aYear);
$aRange="February"; Date(2;1;$aYear);
$aRange="March"; Date(3;1;$aYear);
$aRange="April"; Date(4;1;$aYear);
$aRange="May"; Date(5;1;$aYear);
$aRange="June"; Date(6;1;$aYear);
$aRange="July"; Date(7;1;$aYear);
$aRange="August"; Date(8;1;$aYear);
$aRange="September"; Date(9;1;$aYear);
$aRange="October"; Date(10;1;$aYear);
$aRange="November"; Date(11;1;$aYear);
$aRange="December"; Date(12;1;$aYear);

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

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

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

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.

RangeDateStart ( aRange, aYear )

Comments

Support this website.

Support this website.