How to count the NUMBER OF DAYS OPEN on basis of Picklist Value

Question

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".

Answer 1

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.