- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Working days formula and Crystal 7 - is it possibl...

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

Working days formula and Crystal 7 - is it possible?

Former Member

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

on 04-18-2008 5:56 PM

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

- SAP Managed Tags:
- SAP Crystal Reports

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-28-2008
12:25 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

07-15-2008
12:48 PM

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

07-15-2008
2:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-22-2008
8:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-22-2008
1:48 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-22-2008
1:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-21-2008
9:15 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-21-2008
7:46 PM

Post Author: V361

CA Forum: Formula

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

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-21-2008
5:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

04-18-2008
7:54 PM

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.

Ask a Question

Related Content

- YTD Salary calculation in data action in Technology Q&A
- How to get Total in advance formulas in Technology Q&A
- How to configure file store to a different location in crystal server 2020? in Technology Q&A
- Crystal report CI/CD on azure app service in Technology Q&A
- Export of RPT to af file using ExportOption CrystalDecisions.Shared.ExportFormatType.HTML32 produces in Technology Q&A

Top Q&A Solution Author

User | Count |
---|---|

67 | |

27 | |

10 | |

8 | |

5 | |

5 | |

4 | |

4 | |

4 | |

3 |

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.