Time elapsed between date and time feilds 1 and date and time feilds 2

[Former Member]

Hi,

I'm relatively new to Crystal reports, but have had a little experience with other reporting software in the past.

In a report i'm currently writing I need to have a result of time spent on a visit to a job. In the visits table I have the feilds 'StartedDate' (DD/MM/YYYY hh:mm), 'StartedTime' (hh:mm), 'CompletedDate' (DD/MM/YYYY hh:mm) and 'CompletedTime' (hh:mm) to work with. Unfortunately the 'StartedDate' and 'CompletedDate' feilds have the date info but the time info is all 00:00, hence the database developers have added the further two time feilds.

I was wondering if anyone could suggest a formula that can calculate the time in hours and minutes between the started date/time and the completed date/time - to make it even more complicated the visit might start on day one and finish on day 2, i.e. - Start 01/01/2001 @ 21:00 and End 02/01/2001 @ 05.00.

Hopefully someone can help, It might be quite a simple formula, but as I say i'm quite new to using Crystal and been away from other reporting software for a few years.

Any other info needed, please get in touch.

Thanks,

Kris

[Former Member · Accepted solution]

Assumptions: StartedDate & CompletedDate are datetime fields and StartedTime & CompletedTime are Time fields.

Create a formula with:

datetimevar x := datetime(date({StartedDate}),{StartedTime});
datetimevar y := datetime(date({CompletedDate}),{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.

Edited by: Sanjay Kodidine on Oct 16, 2009 2:23 PM

[Former Member · Accepted solution]

Thanks very much for the reply, but after I insert it and let the program check it - it displays an error.

An error box that says:

 ! A Time is Required Here

and the {VisitLog.StartedTime} (in the first line of code) is highlighted.

Below is the edited code I've placed in Formula Editor - I've had to change the four data references so they fit in with my database.

 datetimevar x := datetime(date({VisitLog.StartedDate}),{VisitLog.StartedTime});
datetimevar y := datetime(date({VisitLog.CompletedDate}),{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.

If it's possible i can send a screen shot if it will help.

Further info:

Database fields   ---   Example data from database
VisitLog.StartedDate  ---  16/10/2009 00:00
VisitLog.CompletedDate  ---  16/10/2009 00:00
VisitLog.StartedTime  ---   09:30
VisitLog.CompletedTime   ---  12:00

Thanks,

Edited by: Kris Glodek on Oct 19, 2009 1:22 PM

Edited by: Kris Glodek on Oct 19, 2009 1:30 PM

[Former Member · Accepted solution]

You need to use Time({VisitLog.StartedTime})...

HTH,

Carl