Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
on Steroids

Crystal Reports: Calculating Business Days

If you look carefully at Seagate Info 7, you'll discovered the IsBusinessDay function is available in the formula editor. But it is no longer available in CR8. We did however, find in the help for DateDiff, a method for calculating the number of business days between two dates.

Local DateTimeVar d1 := {Orders.Order Date};

Local DateTimeVar d2 := {Orders.Ship Date};

DateDiff ("d", d1, d2) -

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

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

The local variables are just there to make the rest of the formula easier to read. The first DateDiff function calculates the number of days between our two dates (as in the beginner hint above), while the next two DateDiff functions subtract the Saturdays and Sundays.

We’ve been asked in the past if it can also allow for public holidays. The only way we can see to do this is to write a User Function Library. Let us know if you’d like this.

You can also calculate the day number.

This useful function comes from Charliy Nash. This function can calculate the day number in the year. This can be useful in payroll or other date-based systems

Function (datevar tdate)
// DOY - Day of Year
// INPUT: Date to be evaluated
// OUTPUT: Julian day of the year
// Example: DOY(11–Feb-2003) = 42 (tdate - date(year(tdate),1,1))+1 ;

But what if you want to calculate the week number?

A customer wanted to identify the week number of a dated transaction. This year January 1 is a Sunday, so it’s quite easy to calculate the days since Jan one and divide by 7. But what if you wanted the matching week number in a different year?

The "DatePart" function has some really powerful features here.

DatePart("ww",{Table.DateField},crSunday) returns the week number and assumes the week starts on a Sunday. Use different day of the week constants (crMonday, crTuesday, etc) if your weeks start on a different day. If you omit the day of week constant then Crystal assumes Sunday is the start of the week.

A variation on this is to use First Week of Year constants as in DatePart("ww",{Table.DateField},crFirstJan1). This sets the rule for when the first week of the year is calculated. Valid values are:

  • crUseSystem – use the system setting
  • crFirstJan1 – start with the week with January the 1st in it.
  • crFirstFourDays – start with the first week that has at least four days in the new year
  • crFirstFullWeek – start with the first complete week.

 

See also:

 

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.

Authorship

Except where an author's name is given at the start of the article, all of these articles were written by Mo Naughton or Bruce Ferguson and edited by Mark Lamendola. Mo is a Crystal Reports consultant, trainer, and developer for Chelsea Technologies, Inc. Bruce Ferguson is a Crystal Reports consultant, trainer, and developer for CrystalKiwi, Inc. Mark Lamendola is a writer and editor with over 15 years experience in professional and trade publications.

 

 

 

 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.