Need help again - adding time fileds

[jhanson2]

Hi again,

I am running CR 2008 and was just given a "project" to create a report that pulls ticket data as well as the amount of time spent working on each ticket. Our ticketing system is Remedy. The problem I'm running into is that the field {TicketSystem.Assignee Effort Duration} is a string. No problem, I think to myself, I'll just convert it to a Time field using cTime. Of course that turns the field into a time field, but math functions can't be performed. I plan to suppress the actual details and only list Company name, Ticket number, and total research time spent on each ticket. I am also grouping on the ticket number.

Can someone please help me?

 

[Madawc]

math functions can't be performed

Which functions? DateDiff should work for time fields, giving differences in hours, minutes or seconds that can then be summarised. Put such a function in a formula field and then sum that formula field.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[jhanson2]

Thanks for your reply, Madawc. When I mention math functions, I am specifically looking to sum research time for a particular ticket.

Say a technician logs 1hr 30mins of research on Day 1, 45 mins of research on Day 2, and 20 mins of reseach on Day 3. These times are reflected as:
01:30:00 for Day 1
00:45:00 for Day 2
00:20:00 for Day 3

The database field this information is coming from is a string field. I have used [blue]cTime[/blue] to convert the string field to a time field but now need to [blue]sum[/blue] all of these times. That's where I'm having the problem. The other minor problem I am having is when the total research time is more than 24 hours.

Any help with this would be appreciated. Thanks again!

 

[lbass]

These aren't actual times, they are durations, so you should convert them to minutes by using a formula like this:

stringvar x := {table.string};
val(left(x,2))*60+val(mid(x,4,2))

Then you can right click on the formula and insert a sum on it. If you want to convert it back to a string with hours and minutes, you can--but only after summing.

-LB

 

[alexlc]

Sorry to butt in on the thread, but I've just used this formula & it's only partly worked, & I'm not sure why it's falling down.

The formula above is returning incorrect values for some lines of data - for example it's returning 420.00 for both a 7 hour & 7 1/2 hour duration, and 425.00 for a 7 3/4 hours duration.

Any help fixing this would be gratefully received.

 

[lbass]

How does your field display? Please show some samples--show the ones where you are getting funny results is possible.

-LB

 

[alexlc]

Thanks,
It's a time field, which is a DateDiff formula formatted to hh:mm - although I have checked it as hh:mm:ss with the same results.

So 07:00 is returning 420.00 as is 07:30 & the 425.00 is coming back from 07:45.

 

[lbass]

Please show the actual conversion formula you used.

-LB

 

[lbass]

I think you should also check whether your time field is really displaying as you think. I just tested my formula and it worked as expected.

-LB

 

[alexlc]

This is the formula for the DateDiff field

numberVar dur:= datediff("s",{@Start DateTime},{@End DateTime});
numberVar Hrs;
NumberVar Min;
NumberVar Sec;
stringVar HHMMSS;

Hrs:= Truncate(Truncate (dur/60)/60);
Min:= Remainder(Truncate(dur/60),60);
Sec:= Remainder(dur,60);

HHMMSS:= totext(Hrs,"0") + ":" + totext(Min,"00") + ":" + totext(Sec,"00");

HHMMSS

And this is how I have used your formula above

stringvar x:= {@Shift Duration DateDiff};
val(left(x,2))*60+val(mid(x,4,2))

I've just checked back & {@Start DateTime} and {@End DateTime} are both CDateTime ({Shift.Date}, {Shift.Time}) - is that what might be throwing it out?

 

[pj2256]

I also need help with a date/time formula along similar lines. I am horrible at writing formulas! I have two formulas that give me two different date and time. One date/time is when the medication was originally ordered and the second date/time is when the medication was redispensed by the pharmacy. I need to get the difference between these two dates/times in the form of days, hours, and minutes, i.e, 8d 17h 32m. Is this possible? I have tried using the DateDiff formula and it only give me hours, i.e., 143.00. This is the formula I am trying (unsuccessfully to use:

If DateDiff("d",{@OrderDate},{@RXActionInstant})> 1
then DateDiff("d",{@OrderDate},{@RXActionInstant}) +
DateDiff ("h",{@OrderDate},{@RXActionInstant})+
DateDiff ("m",{@OrderDate},{@RXActionInstant})
else
DateDiff ("m",{@OrderDate},{@RXActionInstant})

Hope you can help,
pj2256

 

[lbass]

pj2256--please start your own thread, and please ALWAYS show the content of formulas or any nested formulas.

alexlc,
Why would there be any difference in the two values if the formulas are exactly the same? As I said, place the formulas in the detail section and see what values you are getting.

-LB

 

[alexlc]

Thanks Ibass, sorry, should have made myself clearer, the {@Start DateTime} & {@ End DateTime} fields do reference the Start & End Time fields in the database (Lotus Notes).

I am also putting the results in the details section, and it is throwing up the incorrect times there.

It looksl ike its any result fromthe datediff formula that has minutes in it.

 

[lbass]

Please show the actual content of both start and end formulas. Also show samples of the fields that are used in each of these formulas. Are the fields actually dates and times or are they strings or converted from strings? Please show the original data and identify the datatype as well.

-LB