Excel Workday equivalent functionality in Crystal Reports 2011

[Former Member]

Workday functionality in Crystal Reports:

   The Workday function within EXCEL returns the Serial number of the date before or after a specified number of workdays. This Blog also covers the similar functionality, which can be implemented in Crystal Reports.

Note: The above functionality will take into consideration of weekends & listed holidays.

                The above functionality can be achieved by writing a formula in crystal or through a custom function. The below example shows the implementation of WORKDAY function through a formula.

Example of WORKDAY function in EXCEL: (where 12-Dec-2011 & 23-Dec-2011 are set as holidays)

Steps to achieve WORKDAY functionality in Crystal Reports:

1)      Enter the list of holiday dates in a formula; I have created a formula naming “Holidays”, where I have set 12-Dec-2011 & 23-Dec-2011 as holidays. Following is the formula content (Keep the “Holidays” formula on the report header and suppress the section):

BeforeReadingRecords;

DateVar Array date_holidays := [Date (2011,12,23), Date (2011,12,12)];

0;

 2)      Create another formula to add or subtract a specified number of business days from a specified start date. As compared to excel we need to input following information into the formula to get the desired output

a)    Start Date b)    Number of days c)    Holiday Dates

 I have created “Workdays” formula within crystal reports and following is its content:

WhileReadingRecords;

DateVar Array date_holidays;

DateVar output_date:= date({Sheet1_.Start Date});  // output_date is the startdate value

NumberVar days:= {Sheet1_.Days}; // days=  Is the number of days to add

NumberVar daysadded := 0; // This will get incremented if the holidays fall on the business day

WHILE daysadded<days

Do (output_date := output_date +1;

    if dayofweek (output_date) in 2 to 6 and not (output_date in date_holidays)

        then daysadded:=daysadded+1

        else daysadded:=daysadded);

WHILE daysadded > days

Do (output_date := output_date -1;

    if dayofweek (output_date) in 2 to 6 and not (output_date in date_holidays)

        then daysadded:=daysadded-1

        else daysadded:=daysadded);

);

output_date;

 The screen shot below displays the start date, days and workday column, where start date and days are input values to the formula and workday column is the return value of the formula for each database value.