GetDayOccurrence

[Ken Newell]

I don't know if it was here are somewhere else where someone was asking about a custom function. Anyhow I will post it here and hopefully whoever was asking for it will find it. If not hopefully someone else might find it useful. This function can help you find the 3rd Saturday from today. Comments are in the custom function so hopefully you can rip it apart. Enjoy.

/*

==================================================

05/30/2004 1.0 KLN Original Version

Gives the Nth Occurance from a given date. Returns "" If Improper weekday is

entered or less than 1 reptation (i.e. only calculates forward to a date in the future)

Format for WeekDay is the same as returned by FM with the DayName() function

or can be a standard three letter day format. A date is returned.

TheDate = Date to calculate from

Weekday = Name of day to find

Reps = Number of day cycles in the future to find

==================================================

*/

Let (DayToGet =

Case(

Weekday = "Sunday" or Weekday = "Sun"; 1;

Weekday = "Monday" or Weekday = "Mon"; 2;

Weekday = "Tuesday" or Weekday = "Tue"; 3;

Weekday = "Wednesday" or Weekday = "Wed"; 4;

Weekday = "Thursday" or Weekday = "Thr"; 5;

Weekday = "Friday" or Weekday = "Fri"; 6;

Weekday = "Saturday" or Weekday = "Sat";7;0);

If ( DayToGet=0 or Reps < 1 ; "" ;

If (DayToGet = DayOfWeek ( TheDate ); TheDate + (Reps *7);

If ( DayOfWeek ( TheDate ) < DayToGet; TheDate + (DayToGet -DayOfWeek( TheDate)) + ((Reps-1) * 7) + 1;

TheDate - (DayOfWeek ( TheDate ) - DayOfWeek ( DayToGet ))+ (Reps) * 7) - 1

)))

[-Queue-]

Note that you could possibly turn

Case(

Weekday = "Sunday" or Weekday = "Sun"; 1;

Weekday = "Monday" or Weekday = "Mon"; 2;

Weekday = "Tuesday" or Weekday = "Tue"; 3;

Weekday = "Wednesday" or Weekday = "Wed"; 4;

Weekday = "Thursday" or Weekday = "Thr"; 5;

Weekday = "Friday" or Weekday = "Fri"; 6;

Weekday = "Saturday" or Weekday = "Sat";7;0)

into

Case( PatternCount( "SunMonTueWedThuFriSat"; Left( Weekday; 3 ) ); ( Position( "SunMonTueWedThuFriSat"; Left( Weekday; 3 ); 0; 1 ) + 2 ) / 3; 0 )

Depending on how convoluted the Weekday field can get.

[Ugo DI LUCA]

Hmm..

Case(Patterncount("Sat|Fri|Thu|Wed|Tue|Mon|Sun",Left(WeekDay,3)),(DayOfWeek(TheDate)+Reps-1) * 7 + Date(1, 1, Year(TheDate)) - DayOfWeek(Date(1, 1, Year(TheDate)))-(WordCount(Left("Sat|Fri|Thu|Wed|Tue|Mon|Sun", Position("Sat|Fri|Thu|Wed|Tue|Mon|Sun", Left(WeekDay, 3 ), 0, 1)))-1))

or something alike, should work as well....what the heck am I missing again here...?

[Ken Newell]

I will have to try yours out to see how it works. I choose to format my case statement in a way that I find easier to read. Also by allowing the weekday to be either spelled out or the 3 letter abbr. it allows for input to match the native FM function DayOfWeek or if manually entered a person can enter just the 3 letters.

[Ugo DI LUCA]

Ken,

I'm absolutely not saying mine works better. I'm just trying to figure out what the let( ) function offers here...

The formula I posted doesn't differ a lot from the one you could use to find the date for Sunday given a WeekOfYear and a Year inputs:

[color:"red"] WeekOfYear * 7 + Date(1, 1, Year) - DayOfWeek(Date(1, 1, Year))-6

The ending 6 would lead to Sunday, while 5 to Thursday, 4 to Wednesday, 3 for Tuesday, 1 for Monday and 0 for Sunday.

To get this number from your 'WeekDay' field, I used

WordCount(Left("Sat|Fri|Thu|Wed|Tue|Mon|Sun", Position("Sat|Fri|Thu|Wed|Tue|Mon|Sun", Left(WeekDay, 3 ), 0, 1))) [color:"red"]-1) ,

Which (similarly to JT's suggestion) also account for either abbreviated or full WeekDay inputs.

"Sat" returns 1-1 = 0, "Friday" = 2-1=1, etc.

The first part :

Case(Patterncount("Sat|Fri|Thu|Wed|Tue|Mon|Sun",Left(WeekDay,3))

checks for valid entry only, either Saturday or Sat. You may want to add a " " at the end of the formula if you wish.

(DayOfWeek(TheDate)+Reps-1) , or an adaptation (as I think it could become buggy according to which day of week you are starting from), should give you the ending WeekNumber.

This said, I'd "frankly" like to hear about why I'd use a custom function in this case ?

[Ken Newell]

I would use as a custom function if (1) it was to be re-used in more than one place to prevent having to type the formula every time especially if I found an error I would only need to edit the custom function to get the correct behavior or (2) hide the underlaying logic from the average user as a person with FMP can use the function it takes FMD to see under the hood. I use the let function as a practice in new FM7 items; I was playing with FM7.

[Ugo DI LUCA]

Ken Newell said:

I would use as a custom function if (1) it was to be re-used in more than one place

I should have played a little with 7 before going to this kind of answer(s). This probably make sense. Sorry for the noise, and thanks for sharing this formula.