Amount of mondays etc in week

[Simon4]

Hello,

not all the personel in the company makes use of the same time table, so we have multiple time tables. 

these work like this: 

SUM({<SEQUENCENR={'1'},STARTDATE = {'$(=$(vStartdate))'}>}DEFAULTHOURS)
+
SUM({<SEQUENCENR={'2'},STARTDATE = {'$(=$(vStartdate))'}>}DEFAULTHOURS)
+
SUM({<SEQUENCENR={'3'},STARTDATE = {'$(=$(vStartdate))'}>}DEFAULTHOURS)
+
SUM({<SEQUENCENR={'4'},STARTDATE = {'$(=$(vStartdate))'}>}DEFAULTHOURS)
+
SUM({<SEQUENCENR={'5'},STARTDATE = {'$(=$(vStartdate))'}>}DEFAULTHOURS)

every sequencenr is a day, monay is 1 and friday is 5 and all of the in between. 

a table will look like this

I have to make a formula of the expected hours to be working every month.

I am thinking of doing it like this:

SUM({<SEQUENCENR={'1'},STARTDATE = {'$(=$(vStartdate))'}>}DEFAULTHOURS) * vAmount_of_mondays.

I am unable to find the amount of mondays correctly for the month of vPeildatum ( date variable). how can i achieve this?

I also need to keep a history for two years. Right now my solution is to make 24 vAmount_of_mondays. but this does not seem so effective...

any suggestions?

thanks in advance and kind regards,

Simon

[rbartley]

HI @Simon4 ,

How about creating a calendar table where each date record in the table has a first of month, last of month and day of week field then joining your main table by date.  You should then be able to work out the number of each day of the week in a month.

[Simon4]

thanks for replying. how would i approach this?

[Simon4]

Thanks for replying, how would I approach this?