We help IT Professionals succeed at work.

# Calculate hours/minutes  between 2 fileds in Crystal Reports

on
Medium Priority
871 Views
I've search but can't quite come up with an answer.  I just need to display the number of hours and minutes elapsed between 2 time periods during the same day.  Here is the formula I have currently but not even close to working.

DateDiff ("n", {SALES_SUMMARY.FIRST_CLOCK_TIME}, {SALES_SUMMARY.FIRST_CHECK_TIME})

Thanks for anyone who can help!
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
Your expression gives the difference in minutes.

So you need a formula field as below:(note '\' not '/'

numbervar hrs;
numbervar mins;
hrs:=DateDiff ("n", {SALES_SUMMARY.FIRST_CLOCK_TIME}, {SALES_SUMMARY.FIRST_CHECK_TIME})\60;
mins:=DateDiff ("n", {SALES_SUMMARY.FIRST_CLOCK_TIME}, {SALES_SUMMARY.FIRST_CHECK_TIME}) mod 60;
totext(hrs,0,"") & ":" & totext(mins,"00")

Commented:
datediff('h',{datefield1},{datefield2})

datefield2 should be the greater date other wise difference will be negative

Commented:
peter57r -
Getting close but I need to ignore the date.  The records actually have different dates but I just need to compare the times of the day.

What I mean is 1 date is 12/30/99 6:32am and the other date is 1/1/00 7:06am.  I don't care about the date...just want to get the number of hours and minutes between 6:32am and 7:06am.
CERTIFIED EXPERT
Commented:
Then you need to put
Timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME})
and do the same for each field.

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
instead of using your date fields in the formula use it with the time function of your date field:

eg:

instead of using {SALES_SUMMARY.FIRST_CLOCK_TIME} use time({SALES_SUMMARY.FIRST_CLOCK_TIME}) in the formula.

Commented:
I think I need to change the "DateDiff" as now it say it is looking for a Date and highlights timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME}), What do I change it to?

Here is my formula.

numbervar hrs;
numbervar mins;
hrs:=DateDiff ("n", timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME}), timevalue({SALES_SUMMARY.FIRST_CHECK_TIME}))\60;
mins:=DateDiff ("n", timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME}), timevalue({SALES_SUMMARY.FIRST_CHECK_TIME})) mod 60;
totext(hrs,0,"") & ":" & totext(mins,"00")
CERTIFIED EXPERT

Commented:
That's a nuisance; - to eliminate the date issue then do this:
dateserial(1,1,1) + timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME})
CERTIFIED EXPERT

Commented:
..and the same for each field.

Commented:
Thanks so far but please hang in there with me a bit longer please!

Now it is saying a number should be here... "({SALES_SUMMARY.FIRST_CLOCK_TIME})"

Here is my formula now

numbervar hrs;
numbervar mins;
hrs:=DateDiff ("n", dateserial(1,1,1) +({SALES_SUMMARY.FIRST_CLOCK_TIME}), dateserial(1,1,1) +({SALES_SUMMARY.FIRST_CHECK_TIME}))\60;
mins:=DateDiff ("n", dateserial(1,1,1) +({SALES_SUMMARY.FIRST_CLOCK_TIME}), dateserial(1,1,1) +({SALES_SUMMARY.FIRST_CHECK_TIME})) mod 60;
totext(hrs,0,"") & ":" & totext(mins,"00")
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Use

DateTime(Date(1,1,1), {SALES_SUMMARY.FIRST_CLOCK_TIME})

mlmcc

Commented:
I needed to truncate the final values but came up with this solution.  Thanks everyone for your help!

int ((timevalue ({SALES_SUMMARY.FIRST_CHECK_TIME}) - timevalue ({SALES_SUMMARY.FIRST_CLOCK_TIME})) / 60)
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile