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

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

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&lt;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&gt;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?

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

You've on the right track. Define the field you want to contain the DiaryDate or the word "Prior as a Character field, and give it this formula:

[font="courier"]IF(DateDiff&lt;1,DTOC(DiaryDate),&quot;Prior&quot;)  /font[/quote]The DTOC function will convert the DiaryDate field to a string so you'll always give the Character field Character data (be it a string that looks like a date, or the word Prior), and you'll avoid the Operand Type error.

Excel will then read in the date properly as a date or the word "Prior" without difficulty and further efforts on your part.

Kruncher

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

This worked perfectly.  Thank you!