asked on

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!

peter57r

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")

crgary_tx

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

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

mattkovo

ASKER

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.

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crgary_tx

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.

mattkovo

ASKER

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")

peter57r

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

peter57r

..and the same for each field.

mattkovo

ASKER

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 McCracken

Use

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

mlmcc

mattkovo

ASKER

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)