### Title

Calculation

### Post

Hello, who can help...

I want to have a calculation like this:

**Count weeks** (**In month** (variable like January, February, etc)).

Thanks!

Calculation

Hello, who can help...

I want to have a calculation like this:

**Count weeks** (**In month** (variable like January, February, etc)).

Thanks!

Yep, hate to assume things and then turn out I assumed wrong.

Let's assume two Fields, MonthNumb--number of the month and YearNumb--4digit number for the year.

Let ([ dow = DayofWeek ( Date ( MonthNumb ; 1 ; YearNumb )) ;

days = Day ( Date ( MonthNumb+ 1 ; 1 ; YearNumb) - 1 ) /* number of days in month */

]

Case ( dow = 1 /*Sunday */ ; 4 + (days > 28 ) ;

dow = 2 /*Mon */ ; 5 ;

dow = 3 ; 5 ;

dow = 4 ; 5 ;

dow = 5 ; 5 ;

dow = 6 ; 5 + (days = 31 ) ;

dow = 7 ; 5 + (days > 30 ) ;

) // case

) //letNote: the expressions in parenthesis inside the case function are boolean expressions that add 1 if True and add 0 if False.

Which goes back to my first question as to what makes up a week. This was information you didn't share with me in your original posts.

Let ([ d1 = Date ( MonthNumb ; 1 ; YearNumb ) ;

dow = DayOfWeek ( d1 ) ;

days = Day ( Date ( MonthNumb + 1 ; 1 ; YearNumb ) - 1 ) /* number of days in month */

] ;

Case ( dow = 1 /*Sunday */ ; 4 ;

dow = 2 /*Mon */ ; 5 + ( days = 31 );

dow = 3 ; 5 ;

dow = 4 ; 4 + ( Days > 28 ) ;

dow = 5 ; 4 ;

dow = 6 ; 4 ;

dow = 7 ; 4 ;

) // case

) //letLeap year should not be an issue. The calculation computes the total number of days in the month and then compares that total where needed to adjust the total weeks based on whether the total number of days will create a fractional week more than 3 days long.

My test file showed these results:

Jan 4 weeks

Jun 5 weeks

Nov 5 weeksComparing these against a Calendar for 2010 I get the same number of weeks for each

Jan ( 4 weeks with partial weeks of less than 3 days at beginning and end of month which thus aren't counted. )

Jun ( 5 weeks, partial weeks at beginning and end are more than 3 days and thus are counted in this total. )

Nov (**4**weeks, Partial week at beginning is more than 3 days and counts, partial week at end is 3 or less and doesn't)Second pair of parameters should be:

dow = 2 /*Mon */ ;**4**+ ( days = 31 );Great!! It's working!!

Now i wanna make it more complex.......

When i want to know the weeks in a month in a year from a particular date.

Like i want to know the weeks in Jan 2010 from date 15-01-2010 (Variable). (Also with the 3 days but then from the date in the week.)

Can you help me with tis challenge...

Let ([ eom = Date ( Month( DateField ) + 1 ; 1 ; Year ( DateField ) ) - 1 ;

dow = DayOfWeek ( DateField ) ;

days = eom - DateField + If ( dow < 5 ; dow - 1 ; dow - 8 )

] ;

Div ( days ; 7 ) + ( Mod ( days ; 7 ) ≥ 3 )

) //letNote that this simpler expression could be adapted to meet the requirements of the original specification also.

Working!! Great!!

One final question:

I want to know the days in the month for a question earlier. Like <From January 15th to the end of the month> for the following calculation:

*You'll need the year, too.**That can be made with a recursive Custom Function, with a looping script, with a repeating calculation field or even with an unstored calculation.**I like the last way.**So you have 2 number fields ( year and month ) and 1 text field ( DayName ), let give them the names: Y , M and DN**Create a calculation field, name it "result", do not insert any calculation inside it and exit.**Now re-open the calculation editor for that field, insert this calc:**Let([*

$i = $i + 1 ;

D = Date ( M ; $i ; Y )

];

( DayName ( D ) = DN ) + If ( Month ( D + 1 ) = M ; result ; Let ( $i = "" ; "" ) )

)*and make this calculation UNSTORED.*Hope you can help.

So if you Specify Monday, October 5, 2010, you want 4 as a result.

Let ( [ eom = Date ( Month ( DateField ) + 1 ; 1; Year ( DateField ) ) - 1 ;

dow = DayOfWeek ( DateField ) ;

d1 = If ( dow ≤ DayNumber ; DateField + DayNumber - dow ; DateField + 7 + DayNumber - dow )

] ;

Max ( Ceiling ( ( eom - d1 + 1 ) / 7 ) ; 0 )

)This assumes a field, DayNumber that has the number 1-7 for the specified day of the week.

This is very similar to another post you've made: Calculation

Just like that post, you will also need to specify the year.

How do you define "weeks"? Full 7 days from Sunday to Saturday where all 7 days are in the same month or does any fraction of a week that contains days from the specified month and year count as a week?