difference between 2 times on the same day 1

[grom71]

Hi all,
I've spent a fair amount on time on this already, so I dont post something with an obvious answer...

I have a time start field called {PATDRUG.TIME_ARRV} and time finished field called {PATDRUG.TIME_FIN}.

They are both string fields and the time is recorded on the same day.
Somtimes they are both not completed, and sometimes only one of the times is completed.
The time is recorded in the db (sql server) as HH:MM, if no time is entered the field is null.
If a time was entered then deleted then a ":" appears in the field !

I just need to know the difference between both times in hours and minutes...

Should be simple eh ?...
Well not for an amateur like me !
I had managed to do in some instances where all fields are completed, but the problem is accomodating the fact the fields may not be completed sometimes...

Steve

 

[lbass]

If the times are not completed, then what do you want each to default to? Or do you want to eliminate them from the calculation?

-LB

 

[grom71]

Hi LB,
yeah eliminate them as they wont count...
Steve

 

[lbass]

Try:

numbervar mindiff;
if isnull({PATDRUG.TIME_ARRV})or
trim({PATDRUG.TIME_ARRV}) = ":" or
isnull({PATDRUG.TIME_FIN})or
trim({PATDRUG.TIME_FIN}) = ":" then
mindiff := 0 else
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.TIME_ARRV}),datetime(currentdate,time({PATDRUG.TIME_FIN})));
numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
if mindiff = 0 then
0 else
totext(hrs,0,"")+":"+totext(mins,0,"")

-LB

 

[grom71]

Hi,
Thanks for your help....
I'm getting an error at the semi colon.
"The ) is missing"

At the end of this line...

mindiff := datediff("n",datetime(currentdate, time({PATDRUG.TIME_ARRV}),datetime(currentdate,time({PATDRUG.TIME_FIN})));

Steve

 

[lbass]

Sorry, it should be:

mindiff := datediff("n",datetime(currentdate, time({PATDRUG.TIME_ARRV})),datetime(currentdate,time({PATDRUG.TIME_FIN})));

-LB

 

[grom71]

Thanks...

Its now saying "A number is required here"

highlighting the last line of code...

totext(hrs,0,"")+":"+totext(mins,0,"")

Steve

 

[lbass]

Guess I should have tested this! Here you go:

if mindiff = 0 then
"" else
totext(hrs,0,"")+":"+totext(mins,0,"")

-LB

 

[grom71]

Hi,
I'm getting a "bad time format string" error at this point..

time({PATDRUG.TIME_ARRV})

below is the entire formula.

numbervar mindiff;
if isnull({PATDRUG.TIME_ARRV})or
trim({PATDRUG.TIME_ARRV}) = ":" or
isnull({PATDRUG.TIME_FIN})or
trim({PATDRUG.TIME_FIN}) = ":" then
mindiff := 0 else
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.TIME_ARRV})),datetime(currentdate,time({PATDRUG.TIME_FIN})));numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
if mindiff = 0 then
"" else
totext(hrs,0,"")+":"+totext(mins,0,"")


I'm not sure if this makes a difference but the date is not important as it is a string, and not a date/time field...

Thanks for all your help so far !
Steve

 

[lbass]

Does the date always appear as "HH:MM" or ":" or null? YOu might try changing the formula to:

numbervar mindiff;
if isnull({PATDRUG.TIME_ARRV})or
trim({PATDRUG.TIME_ARRV}) in [":",""] or
isnull({PATDRUG.TIME_FIN})or
trim({PATDRUG.TIME_FIN}) in [":",""] then
mindiff := 0 else
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.TIME_ARRV})),datetime(currentdate,time({PATDRUG.TIME_FIN})));numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
if mindiff = 0 then
"" else
totext(hrs,0,"")+":"+totext(mins,0,"")

-LB

 

[grom71]

Hey
Thats great....
Thankyou soooooo much

 

[grom71]

Errrm..
Sorry about this...
I thought this was fixed...

I'm still getting some time values which are over 60 minutes..
eg 7.7 rather than 7 hours and ..minutes....
I've tried to look through the formula but not managed to work out where its going wrong...

Any help greatly appreciated as ever !
Steve

 

[lbass]

I don't think my formula could return such a result, so you had better post your actual formula here, and also explain where you are seeing this result.

-LB

 

[grom71]

OK.
The formula I am using is this:
numbervar mindiff;
if isnull({PATDRUG.TIME_ARRV})or
trim({PATDRUG.TIME_ARRV}) in [":",""] or
isnull({PATDRUG.TIME_FIN})or
trim({PATDRUG.TIME_FIN}) in [":",""] then
mindiff := 0 else
mindiff := datediff("n",datetime(currentdate, time({PATDRUG.TIME_ARRV})),datetime(currentdate,time({PATDRUG.TIME_FIN})));numbervar hrs := truncate(mindiff/60);
numbervar mins := remainder(mindiff,60);
if mindiff = 0 then
"" else
totext(hrs,0,"")+":"+totext(mins,0,"")

For the row which outputs the above formula value of 7:7 the times are as follows:
{PATDRUG.TIME_ARRV} = 09.17
{PATDRUG.TIME_FIN} = 16.24

Rather odd eh ?
Steve

Authorised re-seller of CRD (a powerful scheduling tool for automating Crystal reports)
Visit

http://www.reports-for-healthcare.com

for 10% discount.

 

[lbass]

I believe that 7:7 is the correct result in this case.

-LB

 

[grom71]

Is it saying 7 hours and 7 minutes ?
I interpreted it as 7 hours 70 minutes, which is obviously over 8 hours....

Which line could add in extra zeros so it would say 07:07 if it is 7 hours and 7 minutes ?
Steve

Authorised re-seller of CRD (a powerful scheduling tool for automating Crystal reports)
Visit

http://www.reports-for-healthcare.com

for 10% discount.

 

[lbass]

Change the end of the formula to:

if mindiff = 0 then
"" else
totext(hrs,"00")+":"+totext(mins,"00")

-LB

 

[grom71]

Your a star !
thanks
S

Authorised re-seller of CRD (a powerful scheduling tool for automating Crystal reports)
Visit

http://www.reports-for-healthcare.com

for 10% discount.