Novice Crystal User and dates

[rose12346]

I am writing a report in Crystal Report version 9 and what I need to do is take one date (let's call this date create date) and calculate the days difference between another date (let's call this date ship date). How do I accomplish this and also exclude weekends and holidays? Can this be done in Crystal Report 9?

[jggtz]

Calculating the number of calendar days between two dates is fairly simple. However, if you need to calculate the number of work days, it gets a bit more difficult. Not only do you have to account for weekends, in many instances you also have to account for holidays, which change each year.
The formula below does both. It requires that you enter your list of holiday dates in a separate formula. The list should not include holidays that will occur on weekends since they are already skipped by the formula. However if you do include a weekend holiday the formula will ignore it. The holiday list can include dates several years in advance. There are even ways to have the formula generate a warning when the holiday list is about to expire (ask me about this). To use this formula, simply copy it into your report and put your field names in on the second and third lines in place of the field names I have used. If your fields are DateTime fields you should put your fields within the DATE() function to convert them to dates. If you get an error you can try changing the first line to WhilePrintingRecords.

If you want to include holidays you put your holiday list in the second formula and then place this formula in the report header. Then suppress the formula or the section.

NOTE - this formula counts both the starting date AND the ending date, if both are work days. So if you start on Monday and end on Tuesday, this counts as 2 days.

//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[i] ) in 2 to 6 and 
     Holidays[i] in start to end then Hol:=Hol+1 );
Weeks + 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

Note: Extracted from Ken Hamady site

[jnole]

Also, make sure the holiday field is displayed somewhere on the header. You can change the font to white so that it does not display numbers. there are additional fields needed to handle negative results.
You will need a formula for Days, Holidays, Hours, AdjEnd, and AdjStart. The Days lets you obtain results for Days. The Hours lets you obtain results in Hour format. Have fun! The Adj formulas adjust for any negative results caused by a Holiday or after hours work time.

// days
WhileReadingRecords;
Local DateVar Start := Date ({@Adj Start}); // place your Starting Date here
Local DateVar End := Date ({@Adj End}); // 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[i] ) in 2 to 6 and
Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + 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

//hours
WhileReadingRecords;
NumberVar Days := {@days}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00"); // The start of your work day
TimeVar SetEnd := TimeValue("20:30"); // The end of your work day
TimeVar StartTime := TimeValue({@Adj Start});// The data field that holds your Start
Time
TimeVar EndTime := TimeValue({@Adj End}); // The data field that holds your End Time

Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)

 

// Adj Start 
WhileReadingRecords;
Local DateTimeVar AdjDateTime := {PROBSUMMARYM1.OPEN_TIME}; // Starting date 

TimeVar SetStart := TimeValue( "8:00"); // The start of your work day 
TimeVar SetEnd := TimeValue("20:30"); // The end of your work day 
DateVar Array Holidays;
// If the start date is a holiday or Saturday or Sunday,
// move it back to 8:30 PM on the previous day, until
// it's not a holiday, Saturday or Sunday
while Date (AdjDateTime) in Holidays or
DayOfWeek (AdjDateTime) in [ 1, 7 ] do
AdjDateTime := DateTime (Date (AdjDateTime - 1), SetEnd);
if Time (AdjDateTime) < SetStart then
AdjDateTime := DateTime (Date (AdjDateTime), SetStart);

if Time (AdjDateTime) > SetEnd then AdjDateTime := DateTime (Date (AdjDateTime), SetEnd);
AdjDateTime

 

// Adj End
WhileReadingRecords;
Local DateTimeVar AdjDateTime := {PROBSUMMARYM1.CLOSE_TIME}; // Ending date
TimeVar SetStart := TimeValue( "8:00"); // The start of your work day
TimeVar SetEnd := TimeValue("20:30"); // The end of your work day
DateVar Array Holidays;

// If the end date is a holiday or Saturday or Sunday,
// move it forward to 8 AM on the next day, until
// it's not a holiday, Saturday or Sunday
while Date (AdjDateTime) in Holidays or
DayOfWeek (AdjDateTime) in [ 1, 7 ] do
AdjDateTime := DateTime (Date (AdjDateTime + 1), SetStart);

if Time (AdjDateTime) < SetStart then
AdjDateTime := DateTime (Date (AdjDateTime), SetStart);

if Time (AdjDateTime) > SetEnd then
AdjDateTime := DateTime (Date (AdjDateTime), SetEnd);
AdjDateTime

Note: Extracted from Ken Hamady site except for the AdjEnd and AdjStart formulas.