Crystal Date Difference

DollarBillIA
DollarBillIA used Ask the Experts™
on
I am measuring how long it takes us to process and ship an order, I am using the following formula, which works great.

datediff("d",(datevalue({ebay_data.Paid on Date})),(datevalue({ebay_data.Shipped on Date})))

Except I need it to ignore Saturday and Sunday when figuring the date difference.

Help would be appreciated, thanks!

Bill
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:

Author

Commented:
I had looked at that, but it lost me.  I am not super good at this stuff, and some of the items in his formula threw me.

Bill
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
//Main formula
WhileReadingRecords;
Local DateVar Array Holidays := [Date (2011,12,25), Date (2012,1,1), Date(2012,1,16), Date(2012,2,20) ];

Local DateVar Start := datevalue({ebay_data.Shipped on Date});    // place your Starting Date here
Local DateVar End := {datevalue({ebay_data.Paid on Date});          // 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

Open in new window


Try that one.  You can add more holidays if you want.

mlmcc
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Thank you very much, I'll work with that.  Thanks for spoon feeding me, I need it!

Bill
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Since the formula was provided through the link in vasto's comment, he really should get a split of the points.  I'll reopen the question if you want.

mlmcc
Top Expert 2011

Commented:
Thanks mlmcc. This is very widely used formula and the credit should go to Ken anyway.  I don't think it is necessary to reopen the question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial