Need Formula: If date is 1/1/1700 then change to "N/A" 1

[NewToThis2]

On my report, if the date time field reads 1/1/1700 0:00, then I want that date tiime replaced with the text "N/A". Could someone please help me with a formula? Here is what I tried that made no changes to the report and gave no error message:

if {EMPLOYEE.TERM_DATE}= DateTimeValue (1/1/1700) then 'N/A'

Thanks!

 

[bdreed35]

You formula content should be something like this:

if {EMPLOYEE.TERM_DATE} = CDate(1700,1,1) then
    "N/A"
else
    ToText({EMPLOYEE.TERM_DATE},"MM/dd/yyyy")

You can change the format of the date to whatever you want. Look up the ToText function and then click on the date time format link in the matrix on the page.

~Brian

 

[NewToThis2]

It worked perfectly! Thanks so much for such a speedy response.

 

[NewToThis2]

OK - now I need to build upon your answer. This term date field has termination dates for this year and for previous years. If the term date is the current year I want the record on the report. If the term date is not the current year, I do not want it on the report. Is there a way to add on to the formula you just provided to meet this criteria as well?

Thanks again for all of your help!

 

[bdreed35]

This logic below should be inserted into your Record Selection Criteria.

Report, Selection Formulas, Record.
Add this logic in the editor:

{EMPLOYEE.TERM_DATE} in CDate(year(currentdate),1,1) to dateadd("yyyy",1,CDate(year(currentdate),1,1))
or
{EMPLOYEE.TERM_DATE} = CDate(1700,1,1)

This will include those records that have the default date in them. If you no longer need/want them, then drop the OR portion and below from the logic above. You probably won't need the formula anymore after since 1/1/1700 will never be returned to the report. If that is the case, remove the formula and just drag the Term Date onto the report.

~Brian

 

[NewToThis2]

Thanks Brian - worked like a charm!