Creating a Custom Formula to Calculate Age
Jun 27, 2005
When working with date fields there’s often a need to calculate the number of days since a particular event took place. For example, if you run a support organization you might want to calculate the number of days it takes to resolve a case.
This is a little bit tricky because if the case is still open you’ll want to calculate the difference between the create date and the current date. If the date is closed, it’s the difference between the create date and the close date.
The formula field below accounts for both situations and even rounds down to the nearest integer. The end result is a simple standardized number which is easy to work with in reports and dashboards.
Formula:
IF( {!IsClosed} , ROUND({!ClosedDate} -
{!CreatedDate}, 0) ,
ROUND((NOW() - {!CreatedDate}),0))
- This formula uses the special Boolean field {!IsClosed} to determine whether the case is open or closed. Note that when you use a Boolean field in an IF function, you do not need to compare it to TRUE or FALSE.
- {!CreatedDate} and {!ClosedDate} are both date/time fields, so you can use them together with the NOW() function in subtraction operations.
- The ROUND function is used to round off the decimal portion of the resulting duration in days.
Note that this is general approach and could be used for calculating age of any type of data -- Leads, Accounts, custom objects, and so on.
Bookmark
Although all Date and Date/Time calculations return a value in days, you can easily modify your Case Age formula to display time information in minutes instead of days. If you multiply the number of days by 24, then multiply that number by 60, you will have that information displayed in minutes instead of days.
To do this, you'll just need to take the two calculations and add in the appropriate mathematical operators.
IF( {!IsClosed} , ROUND(
(({!ClosedDate} - {!CreatedDate}) * 24) * 60, 0) ,
ROUND((
((NOW() - {!CreatedDate}) * 24) * 60),0))
For best results when creating this formula, I'd suggest that you set the formula return type as "Number", and select decimal places as "0".
Posted by: spazure | September 09, 2005 at 04:32 AM
Within the lead page, how do i calculate age of lead if there is no "Create date" field. Is there a way to extract the date from the "created by" field.
Posted by: Dave | September 24, 2005 at 06:29 AM
In the formula wizard, you should have access to the {!CreatedDate} field on Lead. I just tried it myself, and it worked.
Try using this formula:
ROUND( NOW() - {!CreatedDate}, 0)
Posted by: Eric Bezar | September 24, 2005 at 12:17 PM
Is there a way to calculate age in business days? We want to track projects through different steps in a process, with the duration of each step in business days only.
Posted by: Jason | September 30, 2005 at 09:27 AM
Is there a way to extract the time from the CreatedDate field? I am interested in reporting on events that have been created between 09:00 and 10:00 each day.
Posted by: Shawn | December 20, 2005 at 03:11 AM
We need to calculate ages on reports but not on a summary but rather on a record by record basis. It appears that we need to do create formula fields on the object and then we can display these fields on a report. Is this correct?
Posted by: Mark Bruso | February 21, 2006 at 09:14 PM
I want to either create a Task or send a Reminder email to the Owner if an Opportunity was created 7 days ago and has not changed Stage.
Doesn't look I can create a rule to do this. Any ideas?
Posted by: Jan | February 22, 2006 at 08:01 AM
Is there any way to calculate the opportunity aging in a specific sales stage?
Posted by: Persio Afonso | March 07, 2006 at 02:15 PM
How can I calculate how many stages an opportunity has been through in my pipeline?
The reason I asked this is that such metric is quickly becoming a great way of avoiding the famous "miracle effect" by ensuring deals are not entered in a pipeline just a few days before they are closed.
Posted by: Persio Afonso | March 07, 2006 at 03:28 PM
Is there a way to calculate age or number of days between a date/time field and a date field?
Posted by: John Johnson | April 04, 2006 at 08:45 AM
@ John: This is a thread in the developer forums about this. Let me know if you have problems accessing that link. You can also check this out: http://www.ihatethebus.com/blog/index.php?entry=entry050831-144419
Posted by: Kingsley | April 04, 2006 at 10:03 AM
How to calculate the number of days it takes to resolve a case without the weeekend and public holiday?
Posted by: May | April 17, 2006 at 08:43 PM
I need to calculate number of minutes a case is open, per business day (from 9AM to 6 PM EST Monday - Friday).
Can we figure this out??
Posted by: Denise Mcdonough | May 02, 2006 at 11:12 AM
We would like to monitor and adjust the initially assumed "Probablity (%)" on each Stage by calculating its actual real value over a period of time.
Is it feasible to calculate it?
Thanks
Posted by: Avi | May 07, 2006 at 10:44 PM
Is there a way to calculate business days between two dates - similar to the NETWORKDAYS function on Excel?
Posted by: Scott Koenigsberg | October 31, 2006 at 05:26 PM
Opportunity Age (days) formula : what am I doing wrong?
I selected Formula Return Type as Number with Decimal places = 0:
IF( {!IsClosed} , {!CloseDate} - {!CreatedDate} , NOW() - {!CreatedDate})
and I get:
"Error: Incorrect parameter for function -. Expected Number, Date, received DateTime"
Posted by: Avi | December 06, 2006 at 05:56 AM
Scott -- the problem with your formula is that CloseDate is a date field and CreatedDate is actually a datetime field. You can only do data arithmetic when both fields are the same type. The good news is that in Winter '07, there will be a DATEVALUE conversion function that allows you to convert a datetime to a date field.
Posted by: Eric Bezar | December 10, 2006 at 10:54 PM
i'm looking for one or more formulas to work out the reading age of a piece of text for my maths coursework. is there any chance of finding out what they are?
Posted by: louise | January 04, 2007 at 05:25 AM
On personal opinion, I find this very helpful.
Guys, I have also posted some more relevant info further on this, not sure if you find it useful: http://www.bidmaxhost.com/forum/
Posted by: alex | March 21, 2007 at 04:57 AM
just amazing
Posted by: shafiq | April 14, 2007 at 02:22 AM
I did a work around for networkdays
IF(MOD( Final_Q_to_Ops__c - DATE(1900, 1, 6), 7) + MAX( Programming_Days__c , Graphic_Development_Days__c )<7, Final_Q_to_Ops__c+MAX( Programming_Days__c , Graphic_Development_Days__c ),Final_Q_to_Ops__c+(MAX( Programming_Days__c , Graphic_Development_Days__c )+(ROUND(MAX( Programming_Days__c , Graphic_Development_Days__c )/5,0)*2)))
Posted by: Jeff Ku | May 14, 2007 at 01:49 PM