Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: 0 hours and 0 minutes Post Reply Post New Topic
Author Message
Hossam
Newbie
Newbie
Avatar

Joined: 31 Mar 2009
Location: Egypt
Online Status: Offline
Posts: 23
Quote Hossam Replybullet Topic: 0 hours and 0 minutes
    Posted: 06 Apr 2009 at 7:25am
hi all ,
 
i use below formula to display diffrent between 2 columns but all values display as 00h 00m
 
right('0'+totext(datediff("h", {INCIDENTSM1.OPEN_TIME} ,{INCIDENTSM1.CLOSE_TIME})),2)+'h '+
right('0'+totext(datediff("n", {INCIDENTSM1.OPEN_TIME} ,{INCIDENTSM1.CLOSE_TIME})),2)+'m'
 
i don't know why
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Apr 2009 at 8:48am
I believe you are trying to get the difference in total time from one field to another and display it as "xxh xxm".
This formula will not do that regardless of the error you are getting. That being said the reason you are getting the 00 is the leading "right" statement which is grabbing the 2 right characters which are the ending "00" from the difference in time. 7:00 am -7:20 am would be "20.00" minutes. The 2 right characters are always "00".
Even with that being corrected I don't think your statment is going to give you what you want.
Example: "8:50 am to 9:00 am" would return "01h 10m" when I think you want it to be "00h 10m"
The following will get you closer but it does not handle all of the instances of lead zeros you might need:
if datediff('n',{INCIDENTSM1.OPEN_TIME},{INCIDENTSM1.CLOSE_TIME}})>60 then "0" +
totext((datediff('n',{INCIDENTSM1.OPEN_TIME},{INCIDENTSM1.CLOSE_TIME})/60),0) + "h " +
totext((remainder((datediff('n',{INCIDENTSM1.OPEN_TIME},{INCIDENTSM1.CLOSE_TIME})),60)),0) + "m" else
"00h " + totext((remainder((datediff('n',{INCIDENTSM1.OPEN_TIME},{INCIDENTSM1.CLOSE_TIME})),60)),0) + "m"
IP IP Logged
Hossam
Newbie
Newbie
Avatar

Joined: 31 Mar 2009
Location: Egypt
Online Status: Offline
Posts: 23
Quote Hossam Replybullet Posted: 06 Apr 2009 at 9:09am
thank you man it working now :)
 
what if need to sum this column in footer of report ??
 
 
thanks in advance
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Apr 2009 at 9:27am
Not 100% sure and you may need to tweak it but try this...
Create a formula field named "minutediff" as:
datediff('n',{INCIDENTSM1.OPEN_TIME},{INCIDENTSM1.CLOSE_TIME})
Create Summary Function as a Sum on this field at your report footer.
Create formula field named "Report Total Summary" as:
if Sum (60">{@minutediff})>60 then totext(((Sum ({@minutediff}))/60),0)
 + "h " +
totext(Remainder((Sum (
{@minutediff})),60),0) + "m" else
"00h " + totext(Remainder((Sum (
{@minutediff})),60),0) + "m"
Place this in your report footer.
IP IP Logged
Hossam
Newbie
Newbie
Avatar

Joined: 31 Mar 2009
Location: Egypt
Online Status: Offline
Posts: 23
Quote Hossam Replybullet Posted: 08 Apr 2009 at 9:08am
Thanks dear it working fine :)
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.016 seconds.