Find Week Day Dates in an Date Range

[Priyabrata]

A function to finds all the dates for a week day within an given date range.

 

For example:

 

We need to find what are the dates for the Thursdays 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

 

And for the invalid dates passed as the function parameters would return "N" that refers to NULL.

 

 

 

 

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

 

Function Body :

 

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";"")

 

 

[Lee Smith]

Isn’t this Custom Function at Brian Dunning’s site called WeekdaysinRange doing the same thing.

Edited January 16, 2014 by Lee Smith
changed the wording.

[Priyabrata]

Oh, there is one for this.
I haven't come up with that.

 

But I still think there are some differences between the parameter passing and there are some variations in output.

Like if user passes incorrect dates as parameter to the function then it returns "N".

 

In the custom function i have created, there no need to format the date parameters through the Date functions, instead it can directly pass the date parameters as text with quotes.

 

Although the WeekdaysinRange seems to be more preferable as it gives the dates for multiple week days passed in the parameters.

 

So i will try more to get the function better.

[comment]

In the custom function i have created, there no need to format the date parameters through the Date functions,

 

I am afraid you are misinterpreting. The Date() function is used in the given example so that the reader can see which dates were used. However, the WeekDaysInRange() function can be called with any two valid dates, for example:

 

WeekDaysInRange ( StartDatefield ; EndDatefield ; WeekDaysfield )

If you are passing invalid dates to the function then you have bigger issues than calculating weekdays in a range. IMHO, it is not the place of the function to detect these issues.

 

[Priyabrata]

Thanks for Clarifying.

Will keep that in mind.

[frediuncle]

Here the 5 refers to the number that represents Thursday in the week.