+ Start a Discussion
Stephen DStephen D 

Use apex/formula to determine a weekend and a holiday

  I want to determine the next business day if a service request is submitted on a weekend.
example: If I submit a service request on a weekend (Saturday/Sunday). The actual date received should be a Monday (which is the business day not Sunday or Saturday). Now if Monday is a holiday, I want the next business day to be the actual date.

I will store this in a different field. I will still keep track of the actual date the service was requested ( Received Date) and populate Business Day with the right date using the formula.

I don't want to use Apex if possible i want to use formulas. We already know the list of Holidays for a calendar year and it is same every other year. The only issue is when a national holiday falls on a weekend (USA) obviously the Monday becomes a holiday. I wonder how it can be handled.

If you have apex solutions too i am willing to hear you out
E Jayaraman IyerE Jayaraman Iyer
Hi Stephen,

The scenario is quite intresting. I want to ask you, how are you planning to store the holiday dates in a field? Are you creating a picklist or text field for it? 

Thanks 
Stephen DStephen D
1. So if I am using a formula, I was thinking of hard coding them as part of the code for the verifications.
2. If it is on Apex, then I will query from salesforce.
After, I just have to assigne the right date to a field called business date for reporting purposes per record.
 
Shiva RajendranShiva Rajendran
Hi Stephen ,
You can use formula field and solve for one day holiday like if request is made for sunday while if monday is holiday , you can shift it to tuesday ..That might be feasible. Lets say a scenerio like someone made service request on satrday , while monday and tuesday are holiday and so the actual request date must be made as wednesday , this means some kindof recursive approach is necessary in formula field. Sadly , i doubt this is feasible for now.
Thanks,
Shiva RV
Stephen DStephen D
Thank you Shiva. you are right. That is what I want to do. How about in Apex, how can this be done.?
Shiva RajendranShiva Rajendran
Hi Stephen ,
In apex it's perfectly possible. Have a custom setting containing the list of holiday's other than saturday, sunday.
You can use the below code snippet.
Trigger triggerName on TriggerObjectName(before insert,before update)
{
Datetime dt = System.now();
String day=dt.format('EEEE');
Datetime newDate=dt;
if(day=='Saturday' )
{
   newDate=dt.addDays(2);
}
else if(day=='Sunday'){
newDate=dt.addDays(1);
}
// check if the newDate exists in the customsetting
//need to implement the doesItExist logic which checks if the newDate is present in the custom setting
while(TriggerHandler.doesItExist(newDate.date()) && (dt.format('EEEE')!='Saturday') && (dt.format('EEEE')!='Sunday') )
{
  newDate= newDate.addDays(1);
}
//set this newDate value
for(TriggerObjectName oo : Trigger.new)
{
  oo.fieldName= newDate;
}
} 
doesItExists sample Implementation :
public class TriggerHandler{
public static void doesItExist(Date dateValue){
Boolean isTrue=false;
// give any custom setting name , have a field having 'date__c'
List<LeaveDate__c> allLeaveDates = LeaveDate__c.getall().values();
for(LeaveDate__c indDate : allLeaveDates )
if(indDate.date__c==dateValue)
{
  isTrue=true;
  break;
}
return isTrue;
}
}

Let me know if you need any help on this.
Thanks,
Shiva RV
 
Stephen DStephen D
Thank you for the code. Don't you want to use the holiday that has been set in salesforce? Would'nt you want to query from the holiday object set in salesforce to make the comparison?
 
Shiva RajendranShiva Rajendran
Hi Stephen,
yes you could do that..that holiday in apex aspect didnt strike me.. that must also work in the same way..only thing is, not sure how they handle satrday and sunday.. i dont think entry for every satrday and sunday will be there..
but yeah its a good solution then custom setting😄
Thanks,
Shiva RV
E Jayaraman IyerE Jayaraman Iyer
Hi Stephan,

As of now we can't use the Holiday Object in the formula field or workflows. Either store the holiday dates in a seperate field and use that field in the formula to check if its a holiday or not. I think you should use apex for this it will be much easier. 

https://success.salesforce.com/ideaview?id=08730000000BpWdAAK

Thanks
Shiva RajendranShiva Rajendran
Yeah Jeyaram.
you are right, but i dont think we can do it in formula field... in apex , holiday object can be used ..
thanks,
Shiva RV