Hello, My questions 1- is there

[antonx]

Hello,

My questions
1- is there a function or formula to determine the week number of a given month (see output example below)
2- is there a function that shows the number of workdays in a given week (see output example below)

My final report is to group them by week number

Input

Trans_date Prem_Amt
Sept 02, 2003 10.00
Sept 03, 2003 10.00
Sept 03, 2003 15.00
Sept 05, 2003 10.00

Sept 08, 2003 10.00
Sept 12, 2003 15.00

Sept 15, 2003 20.00

Sept 22, 2003 10.00
Sept 23, 2003 10.00
Sept 24, 2003 10.00

Output

Trans Week Number of Total Premium
Period working days Amount
Sept 2003 Week 1 4 days $45.00
Sept 2003 Week 2 5 days $25.00
Sept 2003 Week 3 5 days $20.00
Sept 2003 Week 4 5 days $30.00
Sept 2003 Week 5 2 days $ 0.00

For output line of Week 1, the number of working days is 4 because Sept 1, 2003 was a holiday.

Thank you in advance,
Antonx

 

[MJRBIM]

Well, question 1 can be answered with the following formula...

Name : {@WeekOfMonth}
Formula : Int (({Table.Trans_date} - Date (Year ({Table.Trans_date}),Month ({Table.Trans_date}),1))/7) + 1

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

For question 2, the number of workdays in a given week would require a datasource table that contained a record for every "work date" in your organization. Work Dates are too specific to organizations to have a "default" function in Crystal.

You want to create something like the table below, and link it to the transaction table...

Date WorkDate
- - - - - - - - - - - - - - - - -
09/01/2003 0
09/02/2003 1
09/03/2003 1
09/04/2003 1
09/05/2003 1
09/06/2003 0
09/07/2003 0
09/08/2003 1
09/09/2003 1
09/10/2003 1
09/11/2003 1
09/12/2003 1
09/13/2003 0
09/14/2003 0
09/15/2003 1
09/16/2003 1
09/17/2003 1
09/18/2003 1
09/19/2003 1
09/20/2003 0
09/21/2003 0
09/22/2003 1
09/23/2003 1
09/24/2003 1
09/25/2003 1
09/26/2003 1
09/27/2003 0
09/28/2003 0
09/29/2003 1
09/30/2003 1
- - - - - - - - - - - - - - - - -