INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Crystal Reports 11.5

Crystal Reports 11.5

(OP)
I am trying to convert a varchar field that data stored looks like time (02:30:00). i need to convert and then sum the values. Tried using the Val({ContHist.DURATION}) it will sum only the hours and not the Minutes.
Help please

RE: Crystal Reports 11.5

The following will give you the time in seconds:

DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),Time({table.varchar})))

You can then place this in the detail section and right click and insert a sum on it.

-LB

RE: Crystal Reports 11.5

(OP)
Thanks LB, but this yields a "0" value. the issue is the field is a varchar type. The data looks like time (01:30:00). I am trying to extract the value of "1.5", so that I can SUM all values in order to report correctly. Currently I can get a value of "1.00", missing the 1/2 hour values. I inserted you suggestion, but it yields me only a 0.00 value...... Lost!

RE: Crystal Reports 11.5

I understand what you are looking for and the formula should have returned the number of seconds. Please do two things:
1-Confirm that the field is a string by browsing the field to see the data type.
2-Post the exact formula you tried.

-LB

RE: Crystal Reports 11.5

(OP)
Yes, the field is a VARCHAR(14) and it does show up in crystal as such.

if isnumeric(left({ContHist.DURATION},2)) then
tonumber(left({ContHist.DURATION},2))+tonumber(right({ContHist.DURATION},2))/60 else
tonumber(left({ContHist.DURATION},1))+tonumber(right({ContHist.DURATION},2))/60

RE: Crystal Reports 11.5

Ok, now try MY formula, exactly as posted without using your formula conversion. Just plug in {ContHist.DURATION} for {table.varchar}.

-LB

RE: Crystal Reports 11.5

(OP)
I did as you said and get a "Bad rime format string" error when trying to save the formula. I assume that I have some data in the duration field that is not formatted like I expected?

RE: Crystal Reports 11.5

Please show examples of the various ways the field displays.

-LB

RE: Crystal Reports 11.5

(OP)

In the attached .png file, note that the column under the total hours field is like 14:30:00 and it then converts to 14.00 hours, the daily hours are just a SUM of column at the left and getting no minutes. Thank you for you help.

if isnumeric(left({ContHist.DURATION},2)) then
tonumber(left({ContHist.DURATION},2))+tonumber(right({ContHist.DURATION},2))/60 else
tonumber(left({ContHist.DURATION},1))+tonumber(right({ContHist.DURATION},2))/60


RE: Crystal Reports 11.5

I am asking to see what the duration field looks like without any formula conversions. It looks from your formula that it sometimes displays like:
4:30:00, for example.

-LB

RE: Crystal Reports 11.5

1 - Can the duration field be null?

2 - Please post MY formula exactly as you implemented it, by copying it into this thread, so I can troubleshoot it. It should look like my post on 2 Nov 17 20:13.

-LB


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close