FindDay ( StartDate ; EndDate ; DayN )

Find Dates for week days in an Data range.

Average rating: 4.9 (25 votes) Log in to vote

Priyabrata Sahoo, Mindfire Solutions
Mindfire Solutions
http://www.mindfiresolutions.com/

Sample input:

FindDay ("1/1/2014";"1/31/2014";5)

Sample output:

1/2/2014
1/9/2014
1/16/2014
1/23/2014
1/30/2014

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

Substitute
(
   If(GetAsDate(GetAsText(StartDate))  ≤  GetAsDate(GetAsText(EndDate)); 
     Let(
           [
              start = GetAsDate(GetAsText(StartDate));
              end = GetAsDate(GetAsText(EndDate));
              Day = DayN;
               
              Day1 = DayOfWeek(start);
              Diff = Day - Day1;
              FinalDay = If(Diff < 0;start - Abs(Diff) + 7;Start + Diff)
             
           ];If(FinalDay > end;"N"; FinalDay & ¶ & FindDay (start+7;end;Day))
        );
 
     Let(
           [
              start = GetAsDate(GetAsText(StartDate));
              end = GetAsDate(GetAsText(EndDate));
              Day = DayN;
               
              Day1 = DayOfWeek(start);
              Diff = Day - Day1;
              FinalDay = "N"
             
            ];FinalDay
        )
     );¶ & "N";"")

The function finds all the dates for a week day for a given date range.

For example:

We need to find what are the dates for the Thurdays in the date range starting from 1/1/2013 to 1/31/2013.

In this case:

The function would be defined as FindDay ("1/1/2014";"1/31/2014";5)

Here the start date and end date in the function parameters are text.
Here the 5 refers to the number that represents Thursday in the week.

Output:

1/2/2014
1/9/2014
1/16/2014
1/23/2014
1/30/2014

For In Valid dates provided in the date range it will return "N".
For Example:
FindDay ("1/1/2014";"1/33/2014";5)

For Reference:

Sunday: 1
Monday: 2
Tuesday: 3
Wednesday: 4
Thursday: 5
Friday: 6
Saturday: 7

FindDay

Function Parameters :

Parameters Type
StartDate : Text
EndDate : Text
DayN : Number

Comments

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

FindDay ( StartDate ; EndDate ; DayN )

Comments

Support this website.

Support this website.