Tuesday, April 27, 2010

Crystal Reports : Calculating Hours using Dates

You may need to at some point or other calculate the number of hours between two dates. Keep in mind that if you are using a robust back-end database like SQL, a data type also holds the time precision down to the second. So this could be applicable for calculating billing hours, time clock hours, etc.

To make this happen we will be using the magic of the DATEDIFF() function.
Simply put, DATEDIFF calculates the length between two dates using a specified interval.

Let us suppose that our table has two values, {TIMECLOCK.START} and [TIMECLOCK.END}.

Create a formula called {@DAYLENGTH} and insert the following text:

DATEDIFF("n", {TIMECLOCK.START}, {TIMECLOCK.END}/60

The 1st parameter ("n"), specifies we want an interval of Minutes. Other valid intervals are "YYYY" for Years or "m" for Months.
So the code returns the number of Minutes between the two dates and then divides that value by 60, giving the hours.

No comments: