Remove dates from Formula

[djieon]

Hi All,

I have this formula:-

datediff ("n",{RM_JOB.REQUIRED_START_DATE},{RM_JOB.ACTUAL_COMPLETION_DATE})

However I need to modify this to only include weekdays, is this possible?

Then once that is done I have a table called RM_CALENDAR_DAY which has a field in it call CALENDAR_DAY which is a date field and contains single dates for things like christmas day, boxing day etc...I also want these excluded from the datesiff above...is that possible..

Thanks in advance

David.

 

[IanWaterman]

See

http://www.kenhamady.com/form01.html

For the holidays you will need to use a sub report and create a shared array from your holidays table.

YOu can then use the sahred array in the holidays section of the Ken Hamady solution.

Ian

 

[djieon]

Hi Thanks for the reply.

I am getting a boolean answer from using the formula above.

Here is my formula:-

//Main formula
WhileprintingRecords;
Local DateVar Start := (date ({RM_JOB.REQUIRED_START_DATE})); // place your Starting Date here
Local DateVar End := (date ({RM_JOB.ACTUAL_COMPLETION_DATE})); // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

any ideas?

 

[IanWaterman]

Formula must end with

Days;

How are you populating the Array Holidays?

As I said earlier you will need to use a Shared arrya and populate from your holiday table in a subreport.

Ian

 

[djieon]

Hi Ian, thanks for the help so far, im afraid arrays are a bit out of my league!

I have created a subreport which has the dates in it... how do I create a shared array? I have placed the subreport in the details section and the field name with the dates in is a date time field called 'RM_CALENDAR_DATE.CALENDAR_DAY'

The subreport does not have a link to the main report.

Cheers

David.

 

[djieon]

ok have been trying to have a bit of a go at this but coming up against problems.

I have put the subreport in the main report header and within the subreport I have created a formula called 'hols' with the following

//Holiday Listing formula to go into the report header of the report.
whileprintingRecords;
shared DateVar Array Holidays := [
{@todate}
];
0

the 'todate' is just 'date ({myfield})' as its normally a datetime

my main formula is the following:-

//Main formula
WhileprintingRecords;
Local DateVar Start := (date ({RM_JOB.REQUIRED_START_DATE})); // place your Starting Date here
Local DateVar End := (date ({RM_JOB.ACTUAL_COMPLETION_DATE})); // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Days;



however its not working, for instance I have start date of 23/09/2010 and end of 14/03/2011 but the answer from the above is coming back as -3 (id be expecting it to be 120)

Hope someone can help me!

 

[IanWaterman]

Take out the holidays bit and check that the rest of the formula is working.

Where have you placed the formula, place the two date fields you are using along side the formula and make sure they are inputting what you expect.

Ian

 

[djieon]

Hi Ian,

I took the holidays out, that made no difference, but then I changed the very end of the formula to weeks; and that seems to be giving me an answer that I am expecting...however when I the holidays bit back in that is making no difference,

any more ideas?

Thanks for all your help

David.

 

[djieon]

Hi All still having problems with the above...


Can anyone help?

Thanks.

 

[djieon]

Hello, can anybody help with the above? thanks in advance

 

[lbass]

The last line of the formula should be:

Weeks + Days - Hol

-LB