CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com

# Thread: Novice Crystal User and dates

1. Junior Member
Join Date
Jan 2014
Posts
3

## Novice Crystal User and dates

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?

2. Senior Member
Join Date
Jul 2005
Posts
1,083

## Re: Novice Crystal User and dates

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.

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.

Code:
//Main formula
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
Code:
//Holiday Listing formula to go into the report header of the report.
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0
Note: Extracted from Ken Hamady site

3. Junior Member
Join Date
Feb 2015
Posts
11

## Re: Novice Crystal User and dates

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.

Code:
// days
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
Code:
//Holiday Listing formula to go into the report header of the report.
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0
Code:
//hours
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)

Code:
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
if Time (AdjDateTime) < SetStart then

Code:
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

if Time (AdjDateTime) < SetStart then

if Time (AdjDateTime) > SetEnd then

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•