Use Crystal Reports to Calculate the Number of Days Between Two Date Fields

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?