Forum Discussion

MarkDavis's avatar
MarkDavis
Qrew Member
6 years ago

Report Formula - If statement for duration (Deposit Date > Posted Date)

Trying to calculate the duration (TAT) from a deposit date to when it was posted. Have the formula correct but since I have some posted dates (CB Posted Date) that is before deposit date (As Of), it is showing a large value like it would in excel. The dates are correct. Either need to ignore those dates/postings or make it blank. 

If([CB Posted Date]=null,null,
[CB Posted Date]<[As Of],null,
[CB Posted Date]>[As Of],[CB Posted Date]-[As Of])

thoughts??


------------------------------
Mark Davis
------------------------------
Formulas and functions
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Mark,
    I am assuming you have a Formula Duration field.  Give this a try:

    if (  isnull([CB Post]),  null,
    [CB Posted Date]<[As Of], null,
    [CB Posted Date]>[As Of],[CB Posted Date]-[As Of])

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
    • MarkDavis's avatar
      MarkDavis
      Qrew Member
      6 years ago
      Sorry about the delay Don. The formula was correct, had some data for the dates that looked right but somehow was not formatted correctly. Once I corrected it, the formula calculated the TAT. Thanks!!

      ------------------------------
      Mark Davis
      ------------------------------