function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SFIEEESFIEEE 

Custom Formula for # of Days Open - Activities

Can anyone please help me to customize a field for # of days open for Activities?  I need to know the number of days between Due Date and Created Date.  I already contacted SFDC Customer Support and they could not help me figure out the formula, but told me that for this to work I may need a Custom S Control??
 
Thank you,

Sari
ceolstadceolstad
Why wouldn't Due Date - Created Date work?

I just tested a similar formula field with Last Modified Date - Created Date and it seems to work fine. 
Maybe try setting the type of formula field to Number.
SFIEEESFIEEE
When I try to use Due Date, it gives me the following error message: Error: You referenced an unsupported field type called "Date/Time" using the following field: ActivityDate.  I can use "Last Modified Date", but I would prefer to make sure that the Due Date is the true "Close" date.  I appreciate your help!
ceolstadceolstad
Ah, I see.  And now that I tried it myself, I don't even see Due Date listed (or Date, as it sometimes written).

Why don't you try something different?  Instead of subtracting Created Date from Due Date, why not try a combination of things...

If the Task is Open, subtract Created Date from NOW().  Then, once the Task is complete, subtract Created Date from the Last Modified.  You would have to assume that the task owner is on top of his/her game and closing it out on the due date.  I don't know how often this will be true. 

The function would be something similar to:
Code:
IF(Closed = False, Datevalue(Now()) - Created Date, Last Modified Date - Created Date)

 
IF(Closed = False, Datevalue(Now()) - Created Date, Last Modified Date - Created Date)

Another possible alternative... Have you tried using the DATEVALUE function?  Its my understanding that DATEVALUE strips the time from a date/time field.  Maybe that will help. 
SFIEEESFIEEE
I tried to enter your code below, but received the following error message:
Error: Invalid Data.
Review all error messages below to correct your data.
Syntax error. Found 'Created'
 
Can you help me to figure out the right code?  I really appreciate your help!  :smileyhappy:
SFIEEESFIEEE

Ok I just updated your code to the following:

IF(IsClosed = False, Datevalue(Now()) - CreatedDate, LastModifiedDate - CreatedDate)

but I am still receiving an error message that I"m unfamiliar with: Error: Invalid Data.
Review all error messages below to correct your data.
Incorrect parameter for function -(). Expected Number, Date, received DateTime