Calculating Time Differences

[CappsRLO]

I have a Start Time and a End Time field in Crystal.
These are Date/Time fields but formatted as Time such as:
Start Time = 09:10 am
Finish Time = 09:20 am

The calculation formula I use does not output the result in a Time Format. I would like the output to be: 00:10

Also I plan on putting a summary formula for a group of records that calculates the sum of this Total Time field that will also show something like: 00:50

Is this Possible?

 

[lbass]

You should use a formula like the following for your calculations:

datediff("n",{table.startdatetime},{table.enddatetime})

Place this in the detail section and insert summaries on it at whatever group/grand total you like. You can then use a second formula or the display string formula area (format field->common->display string) to convert the results for a string with the desired display. You can use the faq767-3543 as a guide to doing this.

-LB

 

[CappsRLO]

Excellent info and it gets me the time difference between the start and finish just the way I want it but now I am unable to do a Summary on my sys.TimeDiff field. Here is the formula I am using for my TimeDiff:
numberVar dur := datediff("s",{Time.Start TIme},{Time.Finish TIme}); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
stringVar hhmm;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);


hhmm := totext(hrs,"00") + ":" + totext(min,"00");

hhmm

Now when I go to insert a Summary for this field it will only allow me to do a COUNT on my sys.TimeDiff field and not a SUM.

 

[lbass]

No, you must insert the summary on the datediff formula ( or use the summary directly in the formula below), and then format the results using the faq, e.g., for the grand total, it would be:

numberVar dur := sum(datediff("s",{Time.Start TIme},{Time.Finish TIme})); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
stringVar hhmm;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
hhmm := totext(hrs,"00") + ":" + totext(min,"00");
hhmm

For a group subtotal, set dur equal to:
sum(datediff("s",{Time.Start TIme},{Time.Finish TIme}),{Table.groupfield});

-LB

 

[CappsRLO]

Sorry to be a pain or sound like an idiot but i'm still new at this. I copied your reply formula and this is the error I get:
The summary / running total field could not be created.

Not sure if this makes any difference but I'm using Crystal Reports 8.5

 

[lbass]

Please copy the actual formula into this thread, and show the content of any nested formulas. Are the start and endtime from the same field so that you have to use the next or previous function?

-LB

 

[CappsRLO]

The start and end time are different fields.
I have an MS Access DB that has seperate start and end times for different tasks from a 'Time' table so the fields I am pulling into Crystal into the Details section are 'Time.Start Time' & 'Time.Finish Time'.
I created a Formula field called sys.Total and this is the formula I have in sys.Total:
whileprintingrecords;
numberVar dur := datediff("s",{Time.Start TIme},{Time.Finish TIme}); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
stringVar hhmm;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
hhmm := totext(hrs,"00") + ":" + totext(min,"00");
hhmm

I am grouping my report by tasks so each task can have 2 or more lines of Start & Finish times with Totals for each.
Now I am creating a new Formula Field called sys.SubTotal which I need to calculate the sum of the sys.Total field for each Task Grouping. This is the formula I am using in sys.SubTotal:
numberVar dur := sum(datediff("s",{Time.Start TIme},{Time.Finish TIme}),{Tasks.TaskName});//get the seconds between 2 dates
numberVar hrs;
numberVar min;
stringVar hhmm;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
hhmm := totext(hrs,"00") + ":" + totext(min,"00");
hhmm
This is the error I receive when checking the above formula:
The summary / running total field could not be created.

 

[lbass]

Sorry, actually, you need to set up the datediff in a separate formula that you then reference in the variable formula. So create {@ddiff}:

datediff("s",{Time.Start TIme},{Time.Finish TIme});

Then your second formula should be the following (for the group instance):

numberVar dur := sum({@ddiff},{Tasks.TaskName});//get the seconds between 2 dates
numberVar hrs;
numberVar min;
stringVar hhmm;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
hhmm := totext(hrs,"00") + ":" + totext(min,"00");
hhmm

For the report total, remove the group condition in dur.

-LB

 

[CappsRLO]

PERFECT!!! Thank you for your help..