Hello Experts,
I know the formula to calculate the days using DATEDIFF function but what if i want to calculate the time from the same column with condition (Open to Approved) I want to know the time it took from Open to get to Approved Status. Below is the sample records to calculate time. The result should be 25 hours.
ID Date Status
1212 12/12/2016 02:30PM Open
1212 12/12/2016 04:40PM Review
1212 12/13/2016 03:30PM Approved
1212 12/29/2016 01:20PM Closed
Thanks in advance for your help.
You need to use a Datetime variable
Assuming your data is grouped by ID create 3 formula
@reset// place this in group header, header can be suppressed
Whileprintingrecords;
Global datetimevar Open := datetime(0,0,0,0,0,0);
Global datetimevar Approved := datetime(0,0,0,0,0,0);
@eval// place this in detail, formula can be suppressed
Whileprintingrecords;
Global datetimevar Open;
Global datetimevar Approved;
If Status = 'Open' then open:=datetimefield;
If Status = 'Approved' then Approved:=datetimefield;
@Display// place this in group footer
Whileprintingrecords;
Global datetimevar Open;
Global datetimevar Approved;
DateDiff("mm", Open, approved) // returns diff in minutes
Ian
Hi Ian,
Thanks for the solution, i tried to create the formula and placed them accordingly when i refresh the report and navigate through the records i am getting following error ("Argument #1 'DateDiff' is out of range) ..
Thanks
Got my system syntax wrong datediff for minutes should be
DateDiff("n", Open, approved) // returns diff in minutes
Thanks, it worked now, i used 'h' instead since i needed it in hours, but the issue i am facing now is its showing the whole number like 12.00, 22.00. How to add minutes after the hours?
You have to do it in minutes and then convert to hrs and mins
Change display formula and include following
numberVar hrs;
numberVar min;
hrs := Truncate(DateDiff("n", Open, approved)/60);
min := Remainder(DateDiff("n", Open, approved,60);
totext(hrs,"00") + ":" + totext(min,"00") ;