On occasion you may need to exclude Weekend days from a Formula or Workflow Rule Trigger. In this Article we will cover two examples of excluding Weekend days (Saturday and Sunday) from a formula.
Example A: Workflow Formula and Rule Trigger Date
When using a Time Dependent Workflow Action, you may want to only send an Email Alert on Weekdays and exclude Saturdays and Sundays. Although this cannot be accomplished with a Workflow Rule alone, it can be accomplished by creating a Formula field, and using that Field as the Rule Trigger Date for the Workflow.
In this example, we are sending a Follow-up Email to our Sales Reps 3 Days after the Appointment has been run. However, we do not want to send the Email to the Sales Reps during the weekend (exclude emails on Saturday and Sunday).
The solution is to create a Formula field on the object that tells the time based Rule on what Date the email should be sent.
To implement the solution, create a new Formula field on the Appointment object which has an output of Date/Time. Use the below formula to populate value in the field.
CASE(MOD( DATEVALUE(CreatedDate) – DATE(1900, 1, 7), 7),
0, [insert Field] +3,
1, [insert Field] + 2,
2, [insert Field] + 2,
3, [insert Field] + 2,
4, [insert Field] + 4,
5, [insert Field] + 4,
6, [insert Field] + 4, null)
If we break down this formula, “MOD( DATEVALUE(Created Date) – DATE(1900, 1, 7), 7)” this part of the formula will tell what day of the week the Appointment took place.
Since Date(1900,1,7) is a known value “Sunday” if created and the actual date is also a Sunday the expression will return a value “0”. If the created date is “Saturday” the expression will return “6”. Similarly values 1, 2, 3, 4, 5 are assigned for other days of the week. Now that we have determined what day of the week the Appointment was run, adding days is fairly simple. Suppose an Appointment was on a Sunday, we are adding 3 days to that Appointment Date, which is Wednesday. I find it is helpful to reference a Calendar.
In the example below, the Appointment Date (circled in Green) falls on a Thursday (represented in the formula as a four, 4) then count ahead 3 days, or until you reach the first Weekday (circled below in Blue). Note: this is actually 4 Calendar Days after the Appointment Date (true calendar duration highlighted below in Yellow). This is where we get this portion of the formula: If Appointment Date is a Thursday (4), then give Appointment Date + 4 Days: "4, Appointment Start Date Time + 4,"
Continue calculating for each day of the week, until you know all the true number of calendar days needed, based on the day of the week the Appointment took place.
In this example we have named our Field "Result Appt Workflow Date/Time" and referenced the Computed Start Date/Time of the Appointment.
We use this new Field for the Rule Trigger Date in the Workflow Rule.
Example B: Date Formula needs to include Business Days only
In this example, we are estimating the a Date on the Project and looking for the Date that is 8 Days prior to the Project's Start Date, excluding weekends.
CASE(MOD(i360__Start_Date__c - DATE(1900, 1, 7), 7),
0, i360__Start_Date__c-11,
1, i360__Start_Date__c-12,
2, i360__Start_Date__c-12,
3, i360__Start_Date__c-12,
4, i360__Start_Date__c-10,
5, i360__Start_Date__c-10,
6, i360__Start_Date__c-10, null)
If we break down this formula, “MOD( DATEVALUE(Created Date) – DATE(1900, 1, 7), 7)” this part of the formula will tell what day of the week the Start Date is.
Since Date (1900,1,7) is a known value “Sunday” if created and the actual date is also a Sunday the expression will return a value “0”. If the Start date is “Saturday” the expression will return “6”. Similarly values 1, 2, 3, 4, 5 are assigned for other days of the week. Now that we have determined what day of the week the Start Date occurred, we must subtract the 8 days. I find it is helpful to reference a Calendar.
In the example below, the Start Date (circled in Green) falls on a Sunday (represented in the formula as a zero, 0) then count 8 Business Days back (circled below in Blue). Note: this is actually 11 Calendar Days before the Start Date (highlighted below in Yellow). This is where we get this portion of the formula: If Start Date is a Sunday (0), then give me 11 Days prior to the Start Date: "0, i360__Start_Date__c-11"
Continue calculating for each day of the week, until you know all the true number of calendar days different for each day of the week.