Hi All

I was wondering if anyone knew how to calculate the amount of weeks within one month. My goal is to able to determine the date of the first friday of each month

Hi All

I was wondering if anyone knew how to calculate the amount of weeks within one month. My goal is to able to determine the date of the first friday of each month

Let ( [

F1 = [Insert Date Field - it can be any date in the month you are searching such as Date ( 1 ; 1 ; 2013 ) ] ;

MM = Month ( F1 ) ;

YYYY = Year ( F1 ) ;

F2 = DayOfWeek ( F1 ) ;

F3 = Case (

F2 = 6 ; 1 ;

F2 = 5 ; 2 ;

F2 = 4 ; 3 ;

F2 = 3 ; 4 ;

F2 = 2 ; 5 ;

F2 = 1 ; 6 ;

F2 = 7 ; 7 )

] ;

Date ( MM ; F3 ; YYYY )

)

How about:

Let ( [

firstDate = Date ( {month} ; 1 ; {year} )

] ;

firstDate - dayofweek ( firstDate ) + 6

)

Edit: I realized this morning there's an error in this. If the first day of the month is a Saturday, this calc will fail. So here's a corrected version:

Let ( [

firstDate = Date ( {month} ; 1 ; {year} ) ;

weeksDay = DayOfWeek ( firstDate )

] ;

Case ( weeksDay < 7 ; firstDate - dayofweek ( firstDate ) + 6 ; firstDate + 6 )

)

Message was edited by: Mike_Mitchell

nmorales --

Here's my version of the same calculation, almost identical to Taylor's. The 'trick' here is to figure out what day of the week the first day of the specified month is, then add enough days to get to a Friday. All of the suggestions are doing that, just in slightly different ways. I've tried to add comments so you can see what I intended.

I like to use Choose (0 index; 1 index; 2 index ; ) with dates, it's very fast and compact to type. A CASE () is just as good, and Mike skipped all that by just doing simple arithmetic.

I started in December, just to show that FileMaker's date engine will do very useful calculations for you, so you don't need to worry about what happens from Dec. to Jan.

-- Drew Tenenholz

Let ( [

start= Date ( 12 ; 15 ; 2013 ) // whatever your starting date should be

; thisMonth= Month ( start )

; thisYear= Year ( start )

; newDate= Date ( thisMonth + 1 ; 1 ; thisYear ) // Date ( month ; day ; year ) i.e. the first day of the next month

; theDayOfWeekForTheFirst= DayOfWeek ( newDate ) // a number from 1 -7

; offset= Choose ( theDayOfWeekForTheFirst ; 0 ; 5 ; 4 ; 3 ; 2 ; 1 ; 0 ; 6 ) // figure out how many days to add to the 1st to get a Friday

; firstFriday= Date ( thisMonth + 1 ; 1 + offset ; thisYear )

; result= DayName ( firstFriday ) & ", " & Day ( firstFriday ) & " " & MonthName ( firstFriday ) & " " & Year ( firstFriday )

];

result

This is helpful however can you please explain this line

**offset= Choose ( theDayOfWeekForTheFirst ; 0 ; 5 ; 4 ; 3 ; 2 ; 1 ; 0 ; 6 ) // figure out how many days to add to the 1st to get a Friday****; firstFriday= Date ( thisMonth + 1 ; 1 + offset ; thisYear )**I've never used the Choose function but I looked it up and it seems that if the Day is 1 offset would be 5 if day 2 offset is 4? Correct?

So if the first day of the next month is a Wednesday which would return 4 right you would add 2 to get Friday? What if you dont want the first friday. Instead you want the first Monday. Would I create different offsets based on which day they choose?

And if they want the second Friday or Monday I would create offsets for that as well?

so they would be as follows:

offsetSecondFriday = CHoose (

**theDayOfWeekForTheFirst ; 0 ; 12 ; 11 ; 10 ; 9 ; 8 ; 7 ; 13 )****offsetFirstMonday= Choose(****theDayOfWeekForTheFirst ; 0 ; 1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 )**Message was edited by: nmorales

nmorales --

Here's my version of the same calculation, almost identical to Taylor's. The 'trick' here is to figure out what day of the week the first day of the specified month is, then add enough days to get to a Friday. All of the suggestions are doing that, just in slightly different ways. I've tried to add comments so you can see what I intended.

I like to use Choose (0 index; 1 index; 2 index ; ) with dates, it's very fast and compact to type. A CASE () is just as good, and Mike skipped all that by just doing simple arithmetic.

I started in December, just to show that FileMaker's date engine will do very useful calculations for you, so you don't need to worry about what happens from Dec. to Jan.

-- Drew Tenenholz

Let ( [

start= Date ( 12 ; 15 ; 2013 ) // whatever your starting date should be

; thisMonth= Month ( start )

; thisYear= Year ( start )

; newDate= Date ( thisMonth + 1 ; 1 ; thisYear ) // Date ( month ; day ; year ) i.e. the first day of the next month

; theDayOfWeekForTheFirst= DayOfWeek ( newDate ) // a number from 1 -7

; offset= Choose ( theDayOfWeekForTheFirst ; 0 ; 5 ; 4 ; 3 ; 2 ; 1 ; 0 ; 6 ) // figure out how many days to add to the 1st to get a Friday

; firstFriday= Date ( thisMonth + 1 ; 1 + offset ; thisYear )

; result= DayName ( firstFriday ) & ", " & Day ( firstFriday ) & " " & MonthName ( firstFriday ) & " " & Year ( firstFriday )

];

result