Jump to content

GetDayOccurrence


This topic is 6437 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

)))

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6437 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.