• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 851
  • Last Modified:

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!
0
mattkovo
Asked:
mattkovo
  • 4
  • 4
  • 2
  • +1
1 Solution
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
peter57rCommented:
Then you need to put
Timevalue({SALES_SUMMARY.FIRST_CLOCK_TIME})
and do the same for each field.
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now