Name & Parameters: MagicDates ( range ) Description: This one gives you FM-Find Friendly date ranges based on User-Friendly input. Works great with the "range" specified as a script paramete

[mx4px]

Name & Parameters: [color:red][big] MagicDates ( range ) [/big]

Description: This one gives you FM-Find Friendly date ranges based on User-Friendly input. Works great with the "range" specified as a script parameter. The ranges you can use are:

Today

Yesterday

Tomorrow

ThisWeek

LastWeek

NextWeek

ThisMonth

LastMonth

NextMonth

ThisYTD

LastYTD

NextYTD

ThisYear

LastYear

NextYear

ThisQuarter

LastQuarter

NextQuarter

So, say you had a script that put you in Find mode. You could set up a series of buttons on a "Find" layout, each of which ran a script called "SetDate," with the date range options as script parameters. The script could look like this:

SetField: DateField;MagicDates(Get(ScriptParameter))

Perform Find

Special thanks to Excelisys and the Business Tracker for the brilliantly simple way of calculating quarters!!

Sample Input:

MagicDates("LastYTD")

Results:

1/1/2006...8/17/2006

Recursive: no

Formula:

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 )

)

Required Functions:

Author(s): mx4px

Date: 08/17/07

Credits: Andy Frazier, Excelisys

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

Edited August 18, 2007 by Guest