Difference Between Two dates, Skip Weekends - Crystal Reports

For example, Date1 is Promised_ship_date, Date2 is Actual_ship_date, so, when the formula editor comes up for Date1, type in, for example,   {ORDERS.Promised_Ship_date}. Do the same for Date2.

You know, of course, that Crystal will put an "@" sign in front of your formula. When you create the formula names, don't call it @Date1.... Just call it Date1

Date1 is the lower date. Date2 is the greater date. This formula does NOT account for date1 being greater than date2. It's complicated enough as it is.

Create another formula called DateDiff. Copy the following code into it. The "//" will show up as remarks and not execute.

//EXPLANATION OF FORMULA:

//6-DayofWeek ({ @Date1}) is the number of days of Date1 UNTIL the weekend

//DayofWeek({@Date2}) -1 is the number of days of Date2 AFTER the weekend

//the long TRUNCATE formula tests if the dates cross MORE THAN one weekend

//If so, multiply by 5 to get the number of days in those weeks.

IF

(Truncate (({@date2}-@date1}-(6-DayOfWeek({@date1})))/7)*5)>0 then (truncate (({@date2}-{@date1}-(6-DayOfWeek({@date1)))/7)*5

//Add Days until weekend + days after weekend

+6-dayofweek({@date1})+DayOfWeek({@date2})-1

ELSE IF

// It can still cross ONLY one weekend.

//If DOW date1 > DOW date2, then it crosses one weekend. Add days UNTIL weekend plus days AFTER weekend

DayofWeek({@Date1})>DayofWeek({@Date2}) then

DayOfWeek ({@date1})>DayOfWeek ({@date2}) then 6-DayOfWeek({@Date1})+DayOfWeek ({@Date2})-1

ELSE IF

// IF date2 day is less than date1 day, but it still crosses a weekend, take two days off

DayofWeek({@Date1})<=DayofWeek({@Date2}) and {@date2}-{@date1}>6 then

{@date2}-{@Date1}-2

ELSE

// If no weekends crossed at all

{@Date2}-{@Date1}

//END OF FORMULA

Back to Mini Solve Products and more Tips