Calculated field to compare dates or return text

[bradmanning]

I'm trying to use a calculated field for an action tracker. I'm looking to have a field say how long it took to complete an action or if it's incomplete. Here's the code I scraped together:

CASE

WHEN (IsDate([@field:Root_Cause_Identification_Date])=1)

THEN Datediff(day,[@field:Trigger_Date],[@field:Root_Cause_Identification_Date])

ELSE 'Not Complete'

END

I keep getting the following error: Conversion failed when converting the varchar value 'Not Complete' to data type int.

Anyone have any recommendations?

[casyana]

Quote

CASE

WHEN (IsDate([@field:Root_Cause_Identification_Date])=1)

THEN Datediff(day,[@field:Trigger_Date],[@field:Root_Cause_Identification_Date])

ELSE 'Not Complete'

END

You are getting this error, because the result of Datediff is integer, and 'Not complete' is a varchar. However, one calculated field cannot have a different data type. So,  you need to convert first statement result to a varchar:

cast( Datediff(day,[@field:Trigger_Date],[@field:Root_Cause_Identification_Date]) as varchar)