Calculate hours/minutes between 2 fileds in Crystal Reports

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!
mattkovoAsked:
Who is Participating?
 
peter57rCommented:
Then you need to put
Timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME})
and do the same for each field.
0
 
peter57rCommented:
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")
0
 
crgary_txCommented:
datediff('h',{datefield1},{datefield2})

datefield2 should be the greater date other wise difference will be negative
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
mattkovoAuthor 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.
0
 
crgary_txCommented:
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.
0
 
mattkovoAuthor 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")
0
 
peter57rCommented:
That's a nuisance; - to eliminate the date issue then do this:
dateserial(1,1,1) + timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME})
0
 
peter57rCommented:
..and the same for each field.
0
 
mattkovoAuthor 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")
0
 
mlmccCommented:
Use

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

mlmcc
0
 
mattkovoAuthor 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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.