Do you need a report that calculates the number of days between two date fields?
This is a simple Crystal Formula: <Date 1> minus <Date 2>
What if you need to exclude the weekends from your calculation? This formula is a little bit more complex.
Let’s say that you require a Sales history report that shows how many days it takes to ship an order from the day we received the order until the day it is invoiced. You also want to calculate how many days early or late based on the date promised to the customer. Weekends must be excluded from the number of days since no deliveries occur on the weekends.
Calculations needed:
- Days to Ship = Invoice Date - Order Date (Exclude the weekends)
- Days Late = Invoice Date – Promise(Ship) Date (Exclude the weekends)
Crystal Formulas to Exclude weekends
- Set Variables for the fields to be used in the calculation (d1, d2)
- Substitute the variables into the formula as shown below. This example uses Sage 100 AR History Date fields.
- Days to Ship
DateTimeVar d1 := {AR_InvoiceHistoryHeader.OrderDate};
DateTimeVar d2 := {AR_InvoiceHistoryHeader.InvoiceDate};
DateDiff ("d", d1, d2) - DateDiff ("ww", d1, d2, crSaturday) - DateDiff ("ww", d1, d2, crSunday)
- Days Late (Note: Ship Date = Promise Date in Sage 100)
DateTimeVar d1 := {AR_InvoiceHistoryHeader.ShipDate};
DateTimeVar d2 := {AR_InvoiceHistoryHeader.InvoiceDate};
DateDiff ("d", d1, d2) - DateDiff ("ww", d1, d2, crSaturday) - DateDiff ("ww", d1, d2, crSunday)
Have Questions or Need Help with Crystal Reports?
Crystal reports is a very powerful tool that can pull data out of Sage and present it in ways to help you run your business. RKL consultants are happy to write reports for you or train you how to write your own reports. Please reach out to us to help you with your reporting needs.
