Time Difference Problem 1

[dperk001]

First Thanks for all the help this forum brings! I try to read the archives and have already gained alot of knowlege, but I am still stuck with a problem.

Here is my problem.
I have work orders that are time stamped. Some work orders are created that over flow to the next day. I need to caculate the minutes it takes for the work order to be completed. I have no problem if the work order is started and finished the same day. I only have a problem when it goes over a day.

So far I have in my formula
DateDiff("s",{WO.REQUESTTIME},{WO.COMPLETIONTIME}) / 60

^ that gives me my minutes.

I also have in my fields that will show the date.
{WO.REQUESTDATE}{WO.COMPLETIONDATE} but I do not use these as of yet in my formula.

Example a work order on 5/2/04 started at 23:09 then ended the next day at 02:24 5/3/04 gives me
a number of -1246 <rounded up

All of the work orders that carry over to the next day gives me a high negitive number. If anyone can help. I would appreciate it very much or if my original formula is not proper please let me know.

TIA

 

[Madawc]

In Crystal 8.5,

DateDiff("n", {WO.REQUESTTIME},{WO.COMPLETIONTIME})

would give you the time in minutes. ("m" is months.)

I can't see why you are getting negative results, even so.



Madawc Williams
East Anglia, Great Britain

 

[dperk001]

Thanks for the "n" I fugured "s" was for seconds, "d" for day but I had no clue what was for minutes, letter "n" sounds good since you can't use "m" do to it's taken for months.

I believe the negitive is because it is based off minutes in a day.
my start time in minutes would be 1389 my ending time in minutes is 144
Then you take 144-1389 and you get a negitive 1245.

The start and end date do not have a time it reads 0:00

I may have to create and some kind of IF THEN formula based on negitive output to find the difference to the time of 24:00 then add the end time. I guess I have to eat lunch and think about it.

 

[lbass]

I think you need to concatenate your date and time fields, as in:
//{@requestdatetime}:
{wo.requestdate}+{wo.requesttime}

//{@completiondatetime}:
{wo.completiondate}+{wo.completiontime}

Then create a datediff formula:

datediff("n",{@requestdatetime},{@completiondatetime})

-LB

 

[dperk001]

lbass,
Thanks for the tip, I tried to concatenate
//{@requestdatetime}:
{wo.requestdate}+{wo.requesttime}
//{@completiondatetime}:
{wo.completiondate}+{wo.completiontime}

but it asked for a number for wo.requesttime and wo.completion time. I tried to put in TimeValues and still nothing.

I then put what I wanted to do on paper.
Since I dealing with minutes I will convert my numbers to minutes. 1440 min = 24hrs

So I wrote this out on paper. I made a another DateDiff
with wo. requestdate and wo.completiondate
@DAYDIFFERENCE

(({@DAY DIFFERENCE} * 1440) + DateDiff("n",{WO.REQUESTTIME},{WO.COMPLETIONTIME})

So that worked, I have a joyful headache now!
I appreciate all the help!

 

[dtempleton]

Try this:

DateDiff("n", DateTimeValue({WO.REQUESTDATE},{WO.REQUESTTIME}), DateTimeValue({WO.COMPLETIONDATE},{WO.COMPLETIONTIME}))