+ Start a Discussion

Use 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.

However, if you have a solution for apex i am willing to hear you out as well.
Alain CabonAlain Cabon

The problem of your question is "Now if Monday is a holiday, I want the next business day to be the actual date." because either you hard-code the list of exceptions which change each year or you code very complicated formulas and you overcome the compiled size.



Federal Holiday Calendar:
1) Martin Luther King, Jr. Day = Third Monday in January => complicated formula #1
2) George Washington’s Birthday = Third Monday in February => complicated formula #2
3) Memorial Day = Last Monday in May => complicated formula #3
4) Independence Day + Christmas Day + ... = July 4 +  December 25 + ... => formula #4
At the end, you will have a very huge and complicated formula if you want something that is accurate for each year based on the rules above.

A simpler formula will use the exact dates like for the 4th of July or 25th of December (formula #4) for all the dates but you will have to change the formula at the beginning of each year (not a big work either).