Time Value difference in HTML Table

I have an HTML table that I have created inside a crystal report so that I can place it inside an email.  One of the columns contains a formula that calculates the difference between CurrentDateTime and the timestamp field of the record being returned from the database. In order to display the field properly in the HTML table I had to do the following ToText() function, however instead of showing just 47min or even 00h 47min, it shows as 12h 47min.  Of course, if the time difference is greater than 1 hour it properly shows as 1h 47min.

How can I remove the "12h" from being displayed when the time difference is less than 1 hour?


Crystal formula for time difference {@Pending Time}:
Time ((DateDiff ('n', {proposal.timestamp}, CurrentDateTime) / 60) / 24)

//DateDiff("n",{proposal.timestamp},CurrentdateTime)


HTML Code:
WhilePrintingRecords;
Stringvar HTML_Table;
HTML_Table:=HTML_Table+
"<tr>"+

//Here we add the Time column
"<td><div align=""center"">"+
ToText({@Pending Time},"hh'h' mm'min'")+
//Mid (ToText({@Pending Time}),1 ,5 )+
"</div></td>"+

"</tr>"
IO_DorkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
NumberVar MyMinutes := DateDiff ('n', {proposal.timestamp}, CurrentDateTime) ;

IF MyMinutes < 60 Then
Totext ( Truncate (MyMinutes),'00') & 'm'
ELSE
Totext ( Truncate ( MyMinutes / 60), '####') + 'h '+
Totext ( Truncate (Remainder ( MyMinutes,60)),'00') & 'm' ;
0
mlmccCommented:
You could also try capitalizing the 'hh' in the ToText formatting.

mlmcc
0
James0628Commented:
mlmcc's suggestion should work.  HH is the 24 hour time format.  Or you could use H if you don't want the leading 0 (eg. 0 or 3 instead of 00 or 03).

 If you'd like to leave off the hours when it's less than 1 hour, Ido's suggestion would probably work (I haven't tried it).  Another method would be something like this:

//Here we add the Time column
"<td><div align=""center"">"+
(
if {@Pending Time} < Time (1, 0, 0) then
  ToText({@Pending Time},"mm'min'")
else
ToText({@Pending Time},"HH'h' mm'min'")
) +
//Mid (ToText({@Pending Time}),1 ,5 )+
< rest of your formula >

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

IO_DorkAuthor Commented:
What is proper etiquette or rules when awarding points?  James's solution was the one I used b/c it was easier for me to implement with no additional configuration to the report. Ido's I am sure works, but due to how my report is structured, other report formulas and select statements that point to {@Pending Time} were throwing errors and I did not feel the need to try and workout the errors the report was throwing.  Should I award all points to James, or most to James and some to Ido?

Thanks
0
James0628Commented:
FWIW, mlmcc's suggestion also works, at least partially, in that it would give you "00h 47min", instead of "12h 47min".  00h may not have been your first choice, but your post did sort of imply that that was acceptable (as an alternative to 12h).

 Anyway, I'm not sure what would be "proper", but, FWIW, I do think that Ido deserves some points, and maybe mlmcc too.  And I have no problem with you splitting the points, however you see fit.

 James
0
mlmccCommented:
There is no defined etiquette as such.

You are free to award the points as you see fit.  It is customary when presented with several viable solutions to split the points in some way.  You can provide Expert A (solution used) the BEST ANSWER and say 400 of the 500 points.  Expert B gets 100 for providing a viable solution even if you never really pursued that path.

In some cases a solution may be viable but doesn't fit your criteria as spelled out in the question.   In that case you could state that and not award any points or again provide a small portion to acknowledge the correctness of the solution.

EE is about solutions and answers to issues not effort so to simply award points to all participants would not be appropriate.

mlmcc
0
IO_DorkAuthor Commented:
thanks for the clarification.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.