Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Business Objects: Crystal Reports 1 Formulas FAQ

Formula Help

How do I calculate the number of hours between two dates? by HowardHammerman
Posted: 17 Jul 08

Calculating the Number Of Hours Between Two Dates

Starting at least with Version 8.5 of Crystal Reports the DateDiff function provided an easy way to calculate the number of hours between two dates.

If both the starting and ending date and time are stored in DateTime type fields, the calculation is easy:


Where "start" is the starting datetime and "end" is the ending datetime.  "Type" is a letter or group of letters indicating the time interval you want the calculation to return as shown below:

Value    Description
yyyy    Year
q    Quarter
m    Month
h    Hour
n    Minuet
s    Second

There are additional types as describe in the on-line help.

Here is an example:


The formula calculates the number of minutes between the two fields and then divides the result by 60 to express the total number of hours as a fraction of an hour.  

Some databases do not include datetime fields but rather store dates as date fields and time as character fields.  A common practice is to store times as a five-character field:  HH:MM using the 24-hour clock.  You can still calculate the difference between two events as hours and fraction of an hour by using the following formula:

timeVar starttime := Time(val({table.STIME}[1 to 2]),val({table.STIME}[4 to 5]),0);
dateTimeVar start := Datetime({table.SDATE},starttime);
timevar endtime :=   Time(val({table.ETIME}[1 to 2]),val({table.ETIME}[4 to 5]),0);
datetimevar end :=   Datetime({table.EDATE},endtime);
DateDiff ("n",start ,end )/60;

The first and third lines create a time variable from the character fields STIME and ETIME respecrtively.
The second and fourth lines create a datetime variable from the date variables SDATE and EDATE and the time variables starttime and endtime.  The last line calculates the number of minutes between the start and end and converts it into hours and fractions of an hour.


Back to Business Objects: Crystal Reports 1 Formulas FAQ Index
Back to Business Objects: Crystal Reports 1 Formulas Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close