Calculating business days between two dates 1

[levinll]

I'm trying to get the below formula to work but I keep getting an error message(A loop was evaluated more than the maximum number of times allowed. ).

Any help would be greatly appreciated.

Local DateTimeVar d1 := {HoursOverDueNoNationalLogistics.DUE_DATE};

Local DateTimeVar d2 := CurrentDate;

Local numberVar DaysToCompare:= DateDiff ("d", d1, d2) ;

Local numberVar LoopCounter := 0;

Local numberVar DaysDifference := 0;

while LoopCounter <= (DaysToCompare - 1) do (
if (Day (d1) <> crSunday) and (Day(d1) <> crSaturday) then
DaysDifference = DaysDifference + 1;

d1 = DateAdd ("d", 1, d1);
LoopCounter = LoopCounter + 1;
);

DaysDifference;

 

[lbass]

Please see Ken Hamady's solution:

http://kenhamady.com/form01.shtml

-LB

 

[synapsevampire]

Please post your software version.

Check out Ken Hamady's (One of the Crystal gurus contributing here) formula:

http://kenhamady.com/form01.shtml

-k

 

[levinll]

We're getting close. It seems the formula doesn't like the way my original date is formatted (A date is required here).

Local DateVar Start := {HoursOverDueNoNationalLogistics.DUE_DATE};

Is there a function that will convert a MM/dd/yyyy formatted field into one that Crystal will use in this formula ?

 

[lbass]

This isn't a matter of format, but of datatype. Browse the field or run your mouse over it in the detail section and observe the tooltip where it will tell you the datatype.

-LB

 

[synapsevampire]

Post the data type and an example of what is in there.

If you state that something is a date, that means that it's a date type, if it's a string representing a date, show the format.

If the format is MM/dd/yyyy , create a formula of:

cdate(val(left({table.field},2)),val(mid({table.field},4,2)),val(mid({table.field},7,4)))

Then use this formula in place of the string date field.

-k

 

[levinll]

The data type is specified as a DateTime. The underlying field is from a SQL Server table whose field type is smalldatetime.

 

[poujor]

Using the information from

http://kenhamady.com/form01.shtml

do the following slight modification:
FROM:
Local DateVar Start := {StartDate}; // place your Starting Date here
Local DateVar End := {EndDate}; // place your Ending Date here
TO:
Local DateTIMEVar Start := {StartDate}; // place your Starting Date here
Local DateTIMEVar End := {EndDate}; // place your Ending Date here

/Poul

 

[levinll]

I figured out logic on my own to get this to work. Thanks to all who posted to this thread.