Skip to Content

Time Sum problems


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 :o)

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.



Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 02, 2009 at 09:28 PM

    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.



    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Kris Glodek


      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.


Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.