i wanted to do this functionality in my formula field. There is a PICKLIST field name stage called "OPENED". i want to check how long this pick list is being opened.
In formula editor I'm trying to do with NUMBER/DATE/DATETIME.
IF(ISPICKVAL(StageName, "OEF Clarification required stage"), TODAY() - LastModifiedDate, 0)
im getting this ERROR "
Error: Incorrect parameter type for operator '-'. Expected Number,
Date, received DateTime".
Best Answer
LastModifiedDate is not the correct field to check here. Consider a scenario where the picklist value is set to OPENED 5 days back. A sales user goes ahead and edits this opportunity 2 Days back for some other fields, Now your formula will yield result as 2 which is incorrect. Instead -
Create a Custom Field "Stage Opened Date" of type Date.
Create a Workflow rule that fires if ISPICKVAL( StageName , "OEF Clarification required stage")
In W/f action add a field update on this new field and populate as TODAY()
Create a Formula - Date Since Opened return type Number and Formula : TODAY() - Stage Opened Date
PS: you get error in above formula as TODAY() is a Date field and LastModifiedDate is a datetime field, Use DATEVALUE function to convert a date/time field into a date field.