RKL eSolutions Blog Trends and Insights

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.

Sales-History-Report-2-1

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?

Got Questions

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.

Ask a Question

Karen Hanley

Written by Karen Hanley

Karen Hanley is a Sage Intacct Senior Solution Architect with RKL eSolutions, LLC. Karen is an accomplished professional services consultant with over 25 years’ experience managing the implementations of accounting/ERP software through all phases of the project life cycle. Her areas of expertise are in software configuration, project management, data conversion, documentation, process improvements, reporting, and training. When not working, Karen enjoys traveling, sailing, golfing, and spending time with family and friends.