We help IT Professionals succeed at work.

Calculate hours/minutes  between 2 fileds in Crystal Reports

mattkovo
mattkovo asked
on
Medium Priority
871 Views
Last Modified: 2012-05-06
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

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")
datediff('h',{datefield1},{datefield2})

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

Author

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.

Ask the Experts
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.

Author

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.

Author

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")
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Use

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

mlmcc

Author

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)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.