Need help calculating business days 1

[jhanson2]

All,

I've seen the various threads about how to calculate business days, but being a relative novice Crystal user, I don't know if they apply to my situation.

I am trying to do a datediff on two fields to calculate whether an SLA was hit or missed. The business days are Monday-Friday with no weekends, so if a ticket is opened on Friday and resolved on Monday, the datediff indicates 2 days instead of 4 days.

Any ideas?

Here's what I have so far that calculates all days:

DateDiff ("d", {Ticket.Submit Date}, {Ticket.Last Resolved Date})

 

[Turkbear]

Hi,
This FAQ has the most common formulas:
faq767-4465

If comes from this site:

http://www.kenhamady.com/form01.shtml

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jhanson2]

Turkbear,
Thanks for your reply. The problem I see with using that formula is the start date and end date are not static. The dates vary dependent on the {ticket.submit date} and {ticket.last resolved date} fields. I'm going to try and format the first line of the FAQ you sent me as:

DateVar StartDate := Date({ticket.Submit Date});
DateVar EndDate := Date(ticket.Last Modified Date});

I'll let you know if it works.

 

[jhanson2]

Turkbear,

It seems to work although I'm still verifying. I'm also using the Highlighting Expert to highlight in Red any value from this formula that is greater than 5. Sometimes it is highlighting tickets, other times it isn't. Help?

 

[lbass]

You haven't clarified what the remaining problem is. What about the highlighting isn't as expected?

-LB

 

[jhanson2]

LB,

The issue with the highlighting is that it isn't highlighting all values greater than 5. It highlights, some, but not all of them. The business day formula is working correctly, but the report is for Service Level Agreements of 5 business days to resolve an issue. For those that miss the SLA I wanted them highlighted. Hope this is enough additional detail and thanks for your reply.

 

[lbass]

So the business days formula is correct, but the highlighting isn't? You need to show us the formula you are using for the highlighting.

-LB

 

[jhanson2]

I select the field for the business day SLA formula and through Highlighting Expert I change the value of the field to be greater than 5 and change the background color to Red.

It's important to note I am also using Crystal Reports version 12.0.0.683

 

[lbass]

You didn't answer my first question. I wonder whether this is a rounding issue. If the values are actually 5.2, 5.0, 5.3, perhaps it is highlighting only the first and third, which are greater than 5.0. You might try using a formula instead of the highlighter, by right clicking on the formula->format field->color->background->x+2 and entering:

if currentfieldvalue < 6 then
crNocolor else
crRed

-LB

 

[jhanson2]

Thanks lb. I'll give that a try.

 

[jhanson2]

Ok, LB, that doesn't seem to work either. To answer your first question in your last answer, yes, the business days calculation is working correctly. I have manually verified it across several tickets from different months (I'm only pulling the data from the LastFullMonth). The formula you offered above appears to be doing the same thing the Highlighting Expert was doing.

There doesn't appear to be a rounding issue as I even tried modifying the original business days formula to include 2 decimal places and the result is zeros for both decimal places. I then modified the formula you offered above as:

[blue]if currentfieldvalue < '6' then
crNoColor else
crRed[/blue]

Leaving the single parentheses out around the value 6 produces an error so I added them. I modified this both down to < '1' as well as flipped it around:

[blue]if currentfieldvalue > '5' then
crRed else
crNoColor[/blue]

but nothing seems to be working like it should. I'm not sure if it is because some of the values produces by the business day datediff are two digit numbers or what the issue is with highlighting. Any further help you can offer is greatly appreciated.

Here's the entire formula as used in my report:

[blue]
//Use the following formula, replacing the items in Bold with your start/end date and the items in Italics with your holidays........

WhilePrintingRecords;
//Set the values of Start Date and End Date
DateVar StartDate := Date({MV__ReportingView.Submit Date});
DateVar EndDate := Date({MV__ReportingView.Last Resolved Date});

//Find out the difference in days and subtract the weekends
NumberVar DaysDiff := DateDiff("d",StartDate,EndDate) -
DateDiff("ww",StartDate,EndDate,crsaturday) -
DateDiff("ww",StartDate,EndDate,crsunday);

//Create an array of Holiday dates
Local DateVar Array Holidays := MakeArray(
Date(2009,01,01),
Date(2009,01,19),
Date(2009,05,25),
Date(2009,09,07),
Date(2009,11,26),
Date(2009,11,27),
Date(2009,12,25));

//Loop through the array checking if each holiday is within the dates
Numbervar Counter := 0;
While UBound(Holidays) <> Counter do
(Counter := Counter + 1;
if Not(dayofweek(Holidays[Counter]) in [1,7]) and
Holidays[Counter] in StartDate to EndDate then DaysDiff := DaysDiff -1;

//Display result to 0 decimal places and no thousand separator
totext(DaysDiff,0,"");
[/blue]

 

[lbass]

The problem is that you have converted the number of days to text. Try this:

if val(currentfieldvalue) < 6 then
crNoColor else
crRed

-LB

 

[jhanson2]

Thank you so much, LB. That did the trick. I have verified it against all tickets in the first half of this year and it correctly highlighted the appropriate tickets that missed the SLA.

I suppose if I had posted the original formula in the first place, all of this back and forth could have been avoided.

Thanks again!