0

Currently i have a formula field on case object, which will return the no. of days the case is not touched. Here, i wanted to exclude the weekend days and the public holiday (which are already created in the company information).

Here is my current logic. Any suggestion is appreciated.

IF( IsClosed , null, 
IF(ISNULL(PostDate__c),FLOOR(NOW() -  CreatedDate),
DATETIMEVALUE(TODAY()) - DATETIMEVALUE(PostDate__c))).

Thanks

  • 2
    If you want to exclude holidays you will need to use code AFAIK. This answer will help with that: salesforce.stackexchange.com/questions/135718/… - Either way this has been answered daytimes over and with a little search effort your answers can be found – Eric Sep 29 '16 at 17:42
  • Instead of apex, is it possible to write some logic in formula field itself?? – venky Sep 29 '16 at 17:47
  • 1
    No, you can't use the BusinessHours API from within a formula. – Adrian Larson Sep 29 '16 at 17:56
0

If you want to take the Holiday table into account, you are likely stuck with Apex as your only solution. You cannot access the BusinessHours functionality from within a formula. My answer here can be slightly modified to get the number of days between two Date values.

public class BusinessDays
{
    static BusinessHours bh { get { /*lazy load default*/ } set; }
    public static Integer smartDiff(Date a, Date b)
    {
        return a <= b ? diff(a, b) : diff(b, a);
    }
    public static Integer diff(Date a, Date b)
    {
        Integer elapsed = 0;
        Datetime pointer = Datetime.newInstance(a, Time.newInstance(12, 0, 0, 0));
        Datetime endpoint = Datetime.newInstance(b, Time.newInstance(12, 0, 0, 0));
        while (pointer < endpoint)
        {
            pointer = pointer.addDays(1);
            if (BusinessHours.isWithin(bh.Id, pointer)) elapsed++;
            else pointer = BusinessHours.nextStartDate(bh.Id);
        }
        return elapsed;
    }
}
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.