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

  • What is the formula field type?
    – Mahmood
    Dec 8, 2014 at 6:39

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.