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

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.

To Veteran:

Just found your posting. I will pursue your solution tomorrow. Thank you so much!

Sincerely, Rags

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

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.

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

It's OK. I am not upset - just tired.

Veteran, I am shocked at your behavior.

Nice one, btw. :wink2:

Thank you, animality.

:jester: I ADORE Freudian-slip typos!