Hi All,

My overall goal for my formula is to calculate profit margin between contract start and end dates for working days only. What I have to work with from the database is the start date, the end date and the margin rate.

The first problem is that there is a 6 month sliding scale for the margin amount.

Up to and including 6 Months = Rate 1
6 months to 12 months = Rate 2
12 months to 18 months = Rate 3
18 months to 24 months = Rate 4
24 months onward = Rate 5

The second problem is where the contract ends. Mostly they tend to end on the 6 monthly marker points but I need to take into account the fact it may end before, such as 2 weeks before.

I've included my basic formula that calculates the working days between two dates so I was hoping someone would help me expand on this to calculate the rest.

_________________________________________________________

WhileReadingRecords;
Local NumberVar Weeks;
Local NumberVar Days;

Weeks:= (Truncate ({rsp.EndDate} - dayofWeek({rsp.EndDate}) + 1
- ({rsp.StartDate} - dayofWeek({rsp.StartDate}) + 1)) /7 ) * 5;

Days := DayOfWeek({rsp.EndDate}) - DayOfWeek({rsp.EndDate}) + 1 +
(if DayOfWeek({rsp.StartDate}) = 1 then -1 else 0) +
(if DayOfWeek({rsp.EndDate}) = 7 then -1 else 0);   

Weeks + Days

________________________________________________________

Thanks Ryan. I'll have a look and see if I can get this to work.