Time Sum problems

[Former Member]

Hi,

I've just managed to get a report up and going with the elapsed time between a start DateTime & Time field and a finish DateTime & Time field. I got help from within this forum, the formula I ended up using to calculate this was:

datetimevar x := datetime(date({VisitLog.StartedDate}),time({VisitLog.StartedTime}));

datetimevar y := datetime(date({VisitLog.CompletedDate}),time({VisitLog.CompletedTime}));

//  The above takes the date part of the Started (and Completed) DateTime Fields and combines it with the 

//  Time of Started and Completed Time fields, thus giving you start and complete datetimes.

 

NumberVar z := datediff('s',x,y);

//  Get the difference between the Start and Complete DateTimes in Seconds.

 

totext(int(z / 3600), 0) & ":" &

totext(abs((Remainder (z,3600)) / 60), 0);

//  Convert the total seconds into hours and minutes.

It's working great, however...

Now that I have all the visits our engineers make in a week to various jobs, I need to work out the total time sent within the time period (the report is run with a date range parameter). The problem is the normal 'sum' function won't work as it's a time value rather than a normal numerical value.

I did have a look round the forum for other examples but I couldn't find anything to help so thought I'd once again ask the experts 🐵

An example of what I have..

Job No.       -        Date            -            Site      -     Elapsed Time     -      Status
  1         -            01/01/09        -           CHP       -       1:0           -          complete
  2         -            01/01/09        -           Office    -       2:20         -          complete
  3         -            01/01/09        -           Office    -       1:45         -          started
  4         -            02/01/09        -           Yard      -       0.50 ;        -         complete

I would be looking to create a total time spent using the values within the 'Elapsed Time' column. As I say these figures are generated as per the formula above and the formula name is 'Elapsed Time'.

Any help you can give would be greatly appreciated and once again - I'm sorry if what I'm trying to do seems pretty basic to you guys, it's been a while since I used crystal.

Thanks,

Kris

[Former Member · Accepted solution]

Put the first three functional lines (up through the determination of 'z') into a separate formula, then Sum() that. To show the total time, use the same formula that you're using at the end - except replacing 'z' with the Sum() - to convert the Sum() of the number of seconds to HH:MM.

HTH,

Carl

[Former Member · Accepted solution]

Thanks a lot for your help, Carl.

Can you expand a little on your answer, sorry I'm not to sure how to go about applying the sum() to the whole formula or replacing the 'z' with a sum().

I think I know where your formula is going, but just not sure how to actually input it if you know what I mean - sorry as I say I'm quite new to Crystal, I'd done some report writing before in a different application, but this was a good few years ago now.

Thanks,

Kris

[Former Member · Accepted solution]

Create a formula called @Seconds (basic syntax; I'm not very strong on Crystal Syntax):

dim x, y, z

x = datetime(date({VisitLog.StartedDate}),time({VisitLog.StartedTime}));
y = datetime(date({VisitLog.CompletedDate}),time({VisitLog.CompletedTime}));
z = datediff('s',x,y);
formula = z;

This can now be summed, or aggregated in any other way you want.

Change your original formula above to (no need to duplicate logic...):

totext(int({@Seconds} / 3600), 0) & ":" &
 
totext(abs((Remainder ({@Seconds},3600)) / 60), 0);

And create another formula {@Total Time} as:

totext(int(sum({@Seconds}, {group}) / 3600), 0) & ":" &
 
totext(abs((Remainder (sum({@Seconds}, {group}),3600)) / 60), 0);

This will show the total elapsed time in HH:MM format. (Replace "" with your grouping field.)

HTH,

Carl

[Former Member · Accepted solution]

Hi,

I've just tried the code you suggested and forgive me for my ignorance of coding but I get an error when I test the code within formula workshop.

I wasn't sure if you can paste pictures in the forum so I've a link to a screen shot of the error via googles picasa webite: http://picasaweb.google.com/lh/photo/Qk27KjJWe7dRFSFPyjHjmg?feat=directlink

The error states 'A number, currency amount, boolean, date, time, date-time, or string is expected here' with a part of the code highlighted.

Once again forgive me for my lack of knowledge.

Thanks

Kris

[Former Member · Accepted solution]

Strange... Try this:

dim x as datetime
dim y as datetime
dim z as datetime
 
x = datetime(date({VisitLog.StartedDate}),time({VisitLog.StartedTime}));
y = datetime(date({VisitLog.CompletedDate}),time({VisitLog.CompletedTime}));
z = datediff("s",x,y);
formula = z;

HTH,

Carl

[Former Member · Accepted solution]

Hi Carl,

I'm afraid the same thing keeps happening.

I've got another screenshot for you to look at: http://picasaweb.google.com/lh/photo/JntUCykUAhQ_G6pTQptj3g?feat=directlink

Once again, thanks for all your help.

Kris

[Former Member · Accepted solution]

Kris,

Sorry, I musta been sleeping when I typed that formula! Try this:

dim x as datetime
dim y as datetime
dim z as number
 
x = datetimevalue(datevalue({VisitLog.StartedDate}),timevalue({VisitLog.StartedTime}))
y = datetimevalue(datevalue({VisitLog.CompletedDate}),timevalue({VisitLog.CompletedTime}))
z = datediff("s",x,y)
formula = z

HTH,

Carl

[Former Member · Accepted solution]

Thanks Carl,

I've added this formula as '@Seconds' it worked, all checked out fine, so saved it. I inserted it into my report but it displays 3600.00 seconds where as the answer of the summed time should be 10800.00 seconds.

I realise there is more to be done until I can get what I need, I've added an example of my report as a picture at this weblink http://picasaweb.google.com/lh/photo/8cn-6IvV647_eVAXbk4eqA?feat=directlink

Can you possibly guide me into how to implement this group of formulas into my report?

Thanks for all your help so far..

Kris

[Former Member · Accepted solution]

No, @Seconds = 3600 is correct. Seconds is calculated row by row. It is not an accumulation.

Go back to my 11/3 10:20 AM EST post, and use the last two formulas to display the total time. (You need to Sum() the {@Seconds} field.)

HTH,

Carl

[Former Member · Accepted solution]

Hi,

Thanks a lot.

I'm getting it all in so far.

Can you tell me what is my 'grouping' feild?

And also how do I go about summing a formula - i.e. Sum the {@Seconds} field?

Thanks, Kris

[Former Member · Accepted solution]

Kris,

I'm happy to help, but I think you really need to do some reading (or a class) on how to use Crystal.

Grouping is set up using the Group Expert. Summing of a field can be added directly to a report using the Sigma icon, or another formula can be created and the SUM() function used.

Carl