Calculate time from two different dates from same column

[former_member520272]

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.

[Former Member · Accepted solution]

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

[former_member520272]

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

[Former Member]

Got my system syntax wrong datediff for minutes should be

DateDiff("n", Open, approved) // returns diff in minutes

[former_member520272]

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?

[Former Member]

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") ;