Skip to Content
0

Calculate time from two different dates from same column

Sep 20, 2017 at 12:30 PM

35

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Ian Waterman Sep 20, 2017 at 12:42 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Sep 20, 2017 at 01:20 PM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Got my system syntax wrong datediff for minutes should be

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

0
Former Member

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?

0

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

0