Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Difficulty getting difference between two times Post Reply Post New Topic
Author Message
Andrewarbogast
Newbie
Newbie
Avatar

Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Quote Andrewarbogast Replybullet Topic: Difficulty getting difference between two times
    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

Edited by Andrewarbogast - 09 Oct 2017 at 7:09am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
Andrewarbogast
Newbie
Newbie
Avatar

Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Quote Andrewarbogast Replybullet 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"
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.