Overdue call formula - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Steve GilbertSteve Gilbert 

Overdue call formula

I am trying to come up with a formula that would take be smething like 

Call Date = ActivityDateTime +/- any day in the current week

What has been asked is that the any call made in the current week of the due date not be logged as missed in a report. I know that you can add time to it in this format (ActivityDateTime +0.0833) but can it be done for the current week ?
Best Answer chosen by Steve Gilbert
James GoerkeJames Goerke
Steve, 

Turned out to be an easy change:
 
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
)

Cheers, 

James

All Answers

James GoerkeJames Goerke
Steve, 

I looked into this and I have a solution for you. I had fun coming up with the formula and I'm still tweaking it because it has one flaw which I'll explain below.

Steps to setup:

1) Create a custom field called Completed Date (Date Type)
2) Create a Workflow Rule that triggers when the status changes to Completed the Completed Date field to TODAY()
3) Then, create a formula field called Completed Date within Week of Due Date as a checkbox formula field with the following syntax:
 
IF(
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
)
Note: This formula doesn't take into account if the activity Due Date is closed in the same week in a year away. So, this formula with Check as True if the Due Date and Completed Date are within the same week and False if they aren't, even if they are a year apart and the same week. Example - 4/1/2016 Due Date and 4/2/2017 Completed Date would check the checkbox. I'm still working on getting the formula to filter out the year issue which is honestly an unlikely use case but still something to take into account.


Thanks,

James
James GoerkeJames Goerke
Steve, 

Turned out to be an easy change:
 
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
)

Cheers, 

James
This was selected as the best answer
Steve GilbertSteve Gilbert
thanks my 1st attempt did not work so ill try it again
James GoerkeJames Goerke
Steve, 

This post actually inspired a blog post where I created a unmanaged package with the needed components. You can easily install it from the link in the post.

https://salesforcequest.com/2016/04/02/adding-to-the-backpack-activity-closed-within-the-same-week/

Cheers,

James
Steve GilbertSteve Gilbert
Hi James,

Thanks again. Question on the equation.Can be used with a the Completed Date being changed to a date/time field. I now have bee asked to calculate the actual time that calls have been missed. So it woul dbe coompled date - schduled date..  Thanks
James GoerkeJames Goerke
Sorry for the late reply, been out on vacation. 

So, for your new requirement, do you need to still check the checkbox or are you looking for a number value for the time (e.g. 30 Minutes).

At a high level, you can subtract a Date/Time field from a Date/Time field and then that will give you the number of minutes between those times. You can do (Completed_Date__c - ActivityDate) which should give you the number of minutes between the two dates. From there you can manipulate the number of minutes value further to display it in a more consumable format - https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm 
Steve GilbertSteve Gilbert
Thank you. Here is what I came up with:

Date_and_Time__c - DATETIMEVALUE( Completed_Date__c )

The Date_and_Time__c is the activity created date and I hope that the 2nd part will convert the field that was created on the origional equation to a date/time so that I can complete the subtraction?