Jump to content

# of Fridays in a given time period


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

Recommended Posts

  • Newbies

Given a starting date and an end date, I need to calculate the number of Fridays in the specified time period. I have not been able to do this and need your guidance. This is my first visit to this forum. Thank you for any suggestions!

Sincerely, Rags

Link to comment
Share on other sites

Here is a general formula for counting the number of occurences of any weekday in a range. It is in FMP 7 syntax. For Fridays, Weekday would equal to 6.

Let ( [

d = EndDate - StartDate + 1 ;

w = Div ( d ; 7 ) ;

r = Mod ( d ; 7 ) ;

a = Weekday - DayofWeek ( StartDate ) ;

x = Mod ( a ; 7 )

] ;

w + ( x < r )

)

To adapt the formula to previous versions, you would first need to adjust for the lack of the Div() function, and the different behaviour of the Mod() function:

Let ( [

d = EndDate - StartDate + 1 ;

w = Int ( d / 7 ) ;

r = Mod ( d ; 7 ) ;

a = Weekday - DayofWeek ( StartDate ) ;

x = Mod ( a + 7 ; 7 )

] ;

w + ( x < r )

)

I will leave the task of eliminating the Let() function by replacing the variables with their definitions to you.

Link to comment
Share on other sites

  • Newbies

Based on Veteran’s posted solution, I came up with the following formula:

Int((EndDate - StartDate + 1)/7) + (Mod(6-DayofWeek(StartDate),7) < Mod(EndDate-StartDate + 1, 7))

I have 23 time intervals in my database so far. The attached jpg. file shows the number of Fridays for each recorded time interval as calculated by the formula, compared to the actual values looked up by me in a calendar. As you can see, four of the 23 calculations are incorrect. I have double-checked my manual counting of Fridays. Where have I gone wrong?

Sincerely, Rags

Fridays.jpg

Link to comment
Share on other sites

Sheesh, do I have to do EVERYTHING myself?

You have two mistakes: first, you did NOT adapt the ADJUSTED definition of the x variable:

x = Mod ( a + 7 ; 7 )

Had you done this correctly, your formula would look like this:

Int( ( EndDate - StartDate + 1 ) / 7 ) + ( Mod( Weekday + 7 - DayofWeek( StartDate ) , 7 ) < Mod( EndDate - StartDate + 1 , 7 ) )

and hard-coded for Fridays:

Int( ( EndDate - StartDate + 1 ) / 7 ) + ( Mod( 13 - DayofWeek( StartDate ) , 7 ) < Mod( EndDate - StartDate + 1 , 7 ) )

This should return results similar to your manual counts - except for the example in record #6, where I am quite certain your "Actual" count is the wrong one.

Link to comment
Share on other sites

  • Newbies

To veteran:

I am sorry I upset you - it certainly was not my intent. You are kind to share your expertise, and I apologize for not getting it right the first time. Thank you for showing me where I made my mistake!

Sincerely,

Rags

Link to comment
Share on other sites

This topic is 5699 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.