5 Replies Latest reply on Apr 18, 2013 6:55 AM by nmorales

# Figuring out week number within a specific 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

• ###### 1. Re: Figuring out week number within a specific 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 )

)

• ###### 2. Re: Figuring out week number within a specific month

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

• ###### 3. Re: Figuring out week number within a specific month

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

• ###### 4. Re: Figuring out week number within a specific month

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

• ###### 5. Re: Figuring out week number within a specific month

I tried what I thought and it worked! Thank you so much.  Now I need to figure out the last week of each month. You see Im trying to make it like outlook select the First, Second Third, Fourth or Last Monday Tues Wed Thurs Fri Sat of each month. If you have any ideas. Feel free.

Thanks!