2 Replies Latest reply: May 15, 2014 9:51 AM by Brad Shipman RSS

    Calculated Field with If Statement that has one outcome as Date and one as Text

    Brad Shipman

      Hopefully this hasn't come up before, but the search function didn't give me anything.


      We use a number of review codes for our files.  I run a report each week to project how many of each code will be appearing the following week.  Each code has a date for which it was diaried.


      Some of these codes were diaried for dates prior to the date of the report and for our purposes, I lump these all together into a field

      called "Prior."


      The field for the code's diary date is DiaryDate.


      I also have a calculated field called DateDiff, which is based on the statement Today{}-DiaryDate.


      Initially, I tried to make a field using the statement If{DateDiff<1,"DiaryDate","Prior"}.  The result of this would be that if the diary date were for a date in the future, then the actual date would appear, whereas if it were in the past, the word "Prior" would appear.


      Alas, this formula results in an Operand Type error, so I have been forced to use If{DateDiff>0,"Prior",""} and then I fill in the blank cells with the actual dates in Excel {using =targetcell}.


      Does anyone have a way that I can get the calculated field to do what I want?