working days between two dates 1

[swetham]

Hi,
I want to find the number of working days between two dates ie, startdate and enddate. I have 'startdate','enddate',week_number' parameters in which if week number is given it will calculate startdate and enddate. Now i want to find number of working days excluding second & fourth saturdays and any list of holidays ( for example us holidays). How to write a formula to get the working days? How will we get the list of holidays ie, like i have to store the list of holidays in ddatabase?


Thanks,
sweish.

 

[IanWaterman]

If you had done a search on Working days in this forum you would have seen a number of posts on this subject, most of them leading to this page.

http://kenhamady.com/form01.shtml

Ian

 

[swetham]

I saw those examples but i need to exclude 2nd,4th saturdays and holidays. I am new to crystal reports, can u please give the exact formula?

 

[swetham]

I have placed this formula named "Holidays"

//Main formula
WhileReadingRecords;
Local DateVar Start :={?startdate} ; // place your Starting Date here
Local DateVar End := {?enddate}; // 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 );

Weeks + Days - Hol


and in "Holidays array" formula i placed

//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2010,01,01),
Date (2010,01,03),
Date (2010,01,09),
Date (2010,01,10),
Date (2010,01,14),
Date (2010,01,17),
Date (2010,01,23),
Date (2010,01,24),
Date (2010,01,26),
Date (2010,01,31),
Date (2010,02,07),
Date (2010,02,13),
Date (2010,02,14),
Date (2010,02,21),
Date (2010,02,27),
Date (2010,02,28),
Date (2010,03,07),
Date (2010,03,13),
Date (2010,03,14),
Date (2010,03,21),
Date (2010,03,27),
Date (2010,04,02),
Date (2010,04,04),
Date (2010,04,10),
Date (2010,04,11),
Date (2010,04,14),
Date (2010,04,18),
Date (2010,04,24),
Date (2010,04,25),
Date (2010,05,01),
Date (2010,05,02),
Date (2010,05,08),
Date (2010,05,09),
Date (2010,05,16),
Date (2010,05,22),
Date (2010,05,23),
Date (2010,05,30)
];
0


In this i have placed the complete holidays (including 2nd,4th saturdays,holidays). Now i want to exclude 1st and 3rd saturdays or i want to exclude the dates in the array and calculate the number of days. IS IT POSSIBLE?

 

[IanWaterman]

I think you are going to have to list the 2nd and 4th saturdays together with holidays in your holidays array.

@workingdays

WhileReadingRecords;
Local DateVar Start := {StartDate}; // place your Starting Date here
Local DateVar End := {EndDate}; // place your Ending Date here

Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Days := datediff("d", start, end);

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 - Hol


//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

 

[swetham]

I have listed the 2nd and 4th saturdays also in the holidays list. The above is not working when there is no data in the database for the particular range and another problem is if i am giving startdate as "2010/02/21" and enddate as "2010/02/27" it is showing 6 working days. It should show only 5.

 

[IanWaterman]

If you want to exclude Saturdays you can go back to the original Ken Hamady formula and modify to 6 day weeks and remove the condition that excludes Sundays

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 6;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(End) = 1 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 );

Weeks + Days - Hol


Also no need to list saturdays in your holiday array.

Ian

 

[swetham]

Sorry for asking the same question these many times. Actually my problem is i have given 2nd,4th saturdays,sundays,nortmal holidays in the array. Now i want the working days. Please help me out. Its very urgent.

 

[IanWaterman]

I am now totally confused as to what you want. If you only want to exclude Holidays and 2nd and 4th saturdays and Sundays(list them in the array) then this will work. I subtracted an additional day from the datediff formula.

Did you do as the notes said, with the array. This should be a separate formula in the report header, and not part of the working days formula.

@workingdays

WhileReadingRecords;
Local DateVar Start := {StartDate}; // place your Starting Date here
Local DateVar End := {EndDate}; // place your Ending Date here

Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Days := datediff("d", start, end)-1;

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 - Hol


//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

 

[swetham]

It is working now. The foemula should be replaced as
WhileReadingRecords;
Local DateVar Start := {StartDate}; // place your Starting Date here
Local DateVar End := {EndDate}; // place your Ending Date here

Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Days := datediff("d", start, end);

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

Days - Hol+1

Thank You so much for helping me.


I have one more doubt. If week number is given i want startdate and enddate. For example, 2010 week 1 means jan 4 to jan 10 should come. I should post it in seperate thread or in this thread i can post?

 

[IanWaterman]

Best to start another thread

Ian

 

[swetham]

Thank You so much once again