cancel
Showing results for 
Search instead for 
Did you mean: 

Working days formula and Crystal 7 - is it possible?

Former Member
0 Kudos

Post Author: Tim F

CA Forum: Formula

Hi folks,

Really hope someone can help, I'm struggling with writing a report that needs to show the difference between two dates in working days. I've found the same formula posted here several times but cannot get it to return a logical value in my report. I'm wondering if that might be because I'm using an older version of Crystal? The formula in question is this one:

//Main formulaWhileReadingRecords;Local DateVar Start := ({PPV_COMPLAINTSEH.DTRECD}); Local DateVar End := ({PPV_COMPLAINTSEH.ACTCMPLTD}); 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 );

Has anyone come across an alternative way of doing this, or have any ideas why this formula is not working in my report? Any advice would be much appreciated,

Regards,

Tim

Accepted Solutions (0)

Answers (8)

Answers (8)

Former Member
0 Kudos

Post Author: Tim F

CA Forum: Formula

Hi Charliy - many thanks for all your help with this, really appreciate it. Unfortunately I can't get this method to work for me and I think it's because the field in question is not a date field, it's a formula field that is the difference in days between two date fields. I may well be missing something obvious (I'm a definite newbie in this regard!) but this is causing me a real headache!

Former Member
0 Kudos

did you manage to fix the issue? I am creating the similar report where records should be shown only of working days.

any more suggestions on this issue (I know it has been a long time but just wondering if someone would come up with an alternative ideas?)

Regards

Jehanzeb

Former Member
0 Kudos

not sure if this is what you are looking for, but here is a formula I use to count the number of days between 2 fields....excluding weekends. fyi... both fields are formula fields.

Local DateTimeVar d1 := {@Del Date};

Local DateTimeVar d2 := {@Prom Date};

DateDiff ("d", d1, d2) -

DateDiff ("ww", d1, d2, crSaturday) -

DateDiff ("ww", d1, d2, crSunday)

Former Member
0 Kudos

Post Author: Charliy

CA Forum: Formula

You set up a Running Total. Drag the filed you want Summed, Select Sum as the operation if that is not the default.

Just below that you weill see Radio Buttons that say For Every Record, On Change of Group, On Change of Field, Use a Formula, etc - click the one that says Use a Formula. The Blue Bos to its right will turn Red, click on it, this is where you put your formula: NOT(DATEPART('w',{table.date}) IN [6,7])

Save that, then just decide if you want it reset on a Change of Group, or Never (Grand Total). Give it a name and put it on your report.

Former Member
0 Kudos

Post Author: Tim F

CA Forum: Formula

Hi Charliy - many thanks, but I'm afraid being a relative newbie I'm not entirely sure how I would go about doing what you describe. Could you explain in more detail how I would use a running total to achieve this? Many thanks

Former Member
0 Kudos

Post Author: Tim F

CA Forum: Formula

Hi there, thanks for responding - well first of all I get "the remaining text does not appear to be part of the formula" error. I don't need the holiday functionality of this formula so I delete that part of it and it accepts the formula, but the result is that it returns just zeros and negative figures which seem to bear no connection to the actual number of days between the dates. I'm totally out of my depth here so if you can help I'd be very grateful!

Former Member
0 Kudos

Post Author: Charliy

CA Forum: Formula

You can still use a Running Total, and use the formula to tell it not to count the data from Saturdays and Sundays.

Former Member
0 Kudos

Post Author: V361

CA Forum: Formula

Tim, what kind of error are you getting from that formula. Can you post it ?

Former Member
0 Kudos

Post Author: Tim F

CA Forum: Formula

Thanks for the reply but that isn't quite what I'm looking for - within each record returned by the report, I want a formula to look at 2 date fields, calculate the difference between them and then exclude weekends. Not possible to do a distinct count because I'm not actually counting records, just calculating the difference between two fields in the same record.

Former Member
0 Kudos

Post Author: Charliy

CA Forum: Formula

This would be simple as a Running Total. Do a Distinct Count of the Dates using a formula to not count Saturday or Sunday.