Skip to Content
avatar image
Former Member

Calculate time from two different dates from same column

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 20, 2017 at 12:42 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 20, 2017 at 01:20 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

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