Difficulty getting difference between two times
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22475
Printed Date: 02 May 2024 at 9:16am
Topic: Difficulty getting difference between two times
Posted By: Andrewarbogast
Subject: Difficulty getting difference between two times
Date Posted: 09 Oct 2017 at 6:17am
Hello everyone,
I'm having difficulty getting the difference between two time fields in Minutes. Both are strings {Arrival_time} and {Appointment_time}. They are both strings displaying time as 00:00 AM/PM. Also, {Arrival_time} is sometimes blank.
I used this formula to replace the "" as N/A:
if IsNull({appt_data.time_of_arrival}) then
"<null>"
else
if {appt_data.time_of_arrival} = "" then
"N/A"
else
CStr (Time({appt_data.time_of_arrival}), "h:mm:ss tt")
If I filter out the "N/A" and use the formula:
datediff("n",datetime({@Time arrival time}),datetime({appt_data.appointment_start_time}))
It works. If I bring back the "N/A" I get the "Bad Time Format String" message.
Please help!
Thanks
|
Replies:
Posted By: DBlank
Date Posted: 11 Oct 2017 at 3:00am
what do you want to do with the empty strings?
return a 0?
return a "N/A" string?
|
Posted By: Andrewarbogast
Date Posted: 11 Oct 2017 at 5:09am
|
I was able to figure it out so that it returns a zero but the problem is if I wanted to do an average then the 0 would skew the data. So maybe it needs to return a a "N/A"
|
Posted By: DBlank
Date Posted: 11 Oct 2017 at 7:13am
that wont help your issue with the average.
use a running total as an average with a evaluate formula to exclude rows where IsNull({appt_data.time_of_arrival})
|
|