I geek out over formulas and a fun one came across my desk today. I responded to a question on the Salesforce Answers Community regarding a formula that will allow for the measuring of whether an activity is closed within the same week of the Due Date. The use case here is interesting because most organizations that measure activities also measure whether it was closed. Taking that a step further you can measure if the activity closed on the same date of the Due Date which lets you measure the accuracy of when those activities are actually getting closed out. But, its not always fair to measure just Due Date against Completed Date.
This formula, combined with a custom date field called Completed Date and a Workflow Rule, will check a checkbox if the Due Date is completed within the same calendar week the Activity is marked Completed.
I’ve added this to my Backpack as a unmanaged package you can easily install.
Activity Closed Within the Same Week – https://login.salesforce.com/packaging/installPackage.apexp?p0=04t80000000lT4v
IF( AND( YEAR(ActivityDate)=YEAR(Completed_Date__c), IF( CEILING( ( ActivityDate - DATE( YEAR( ActivityDate ), 1, 1) + 1) / 7) > 52, 52, CEILING( ( ActivityDate - DATE( YEAR( ActivityDate ), 1, 1) + 1) / 7) ) = IF( CEILING( ( Completed_Date__c - DATE( YEAR( Completed_Date__c), 1, 1) + 1) / 7) > 52, 52, CEILING( ( Completed_Date__c- DATE( YEAR( Completed_Date__c), 1, 1) + 1) / 7) ) ), TRUE, FALSE )