Ask Search:
Andrea CampbellAndrea Campbell 

Need help with this "Age" formula

if( ISBLANK(Enr_Completed_Date__c), CEILING(DATEVALUE(CreatedDate) - Enr_Due_Date__c ), CEILING(DATEVALUE(CreatedDate) - DATEVALUE(Enr_Completed_Date__c)))

It should calculate negative numbers from Create Date to a FUTURE Due Date, positive values if the due date has passed (i.e. if create date is today and due date is 2 days from now, Age should be -2...if create date is today and due date today, Age=0...if created date was 2 days ago and due date was yesterday, today, the age should be 1).
After the action is completed (there is a completed date), the counter should stop (freeze).  Status does not play a role.

This formula is working, however, once the completed date is stamped, it is recalculating the age from created date to completed date.  We want the value, whatever it was to freeze after the completed date is stamped.

Thank you!
 
Best Answer chosen by Andrea Campbell
Arijit MajeeArijit Majee
Hi Andrea,

What I understand from your post is that you want to calculate age when Enr_Completed_Date__c is not blank and has a value. what is the data type for Enr_Completed_Date__c ( date or date&Time)?

Try below formula:
if( AND(ISBLANK(Enr_Completed_Date__c),Enr_Completed_Date__c>DATEVALUE(CreatedDate) ),
CEILING(Enr_Completed_Date__c-DATEVALUE(CreatedDate)),
CEILING(DATEVALUE(CreatedDate) - Enr_Due_Date__c ))

 

All Answers

Arijit MajeeArijit Majee
Hi Andrea,

What I understand from your post is that you want to calculate age when Enr_Completed_Date__c is not blank and has a value. what is the data type for Enr_Completed_Date__c ( date or date&Time)?

Try below formula:
if( AND(ISBLANK(Enr_Completed_Date__c),Enr_Completed_Date__c>DATEVALUE(CreatedDate) ),
CEILING(Enr_Completed_Date__c-DATEVALUE(CreatedDate)),
CEILING(DATEVALUE(CreatedDate) - Enr_Due_Date__c ))

 
This was selected as the best answer
Arijit MajeeArijit Majee
Can you provide an example date for Enr_Completed_Date__c and created date and expected age value. So that I can guide you for a better formula.
Andrea CampbellAndrea Campbell
Enr_Completed_Date would be 8/9/2019 11:22 AM
Created Date is actually Create By, with the date: Created By Joe DiNunzio, 8/6/2019 2:53 PM
Expected Age value is a number, no decimals.  It could be a negative number (i.e. -2)

I hope this is what you are asking?  Thanks a lot!
Andrea CampbellAndrea Campbell
@Arijit Majee
Error: Incorrect parameter type for operator '>'. Expected DateTime, received Date
Steve MolisSteve Molis
Just remove the DATEVALUE() wrapper from the Date field in your Formula and you should be all set
Andrea CampbellAndrea Campbell
THANK YOU!!!!