Formula fields: the duct tape of Salesforce… they can do nearly anything: fetch information across objects, perform complicated calculations, concatenate data (to name just a few). Specifically for dates, there are many great functions that can identify the day of the month, the month itself, and the year. What isn’t inherently available is the ability to use a simple function to identify the day of the week. Fortunately, Salesforce (and many Community members and bloggers) have assembled a formula that returns the day of the week.
Let’s take a closer look at the fomula provided by Salesforce. For the purposes of this explanation, we will use dateField as a place holder for a date field.
MOD( dateField – DATE( 1900, 1, 7 ), 7 ),
This formula utilizes two functions: CASE() and MOD().
- CASE(expression, value1, result1, value2, result2,…, elseResult)
This function evaluates an expression (in our formula, the expression is MOD(dateField – DATE(1900, 1, 7), 7) ) and returns a result when the expression equals the corresponding value.
- MOD(expression, divisor)
This function divides an expression by the divisor (in our formula, the expression dateField – DATE(1900, 1, 7) is being divided by 7) and returns the remainder (a la middle school division). E.g. MOD(12,3) would return 0, as 12 divided by 3 is 4; MOD(25,3) would return 1, because 25 divided by 3 is 8 remainder 1.
So what exactly is the formula doing? Why are we using January 7, 1900? The overall methodology is to find the number of days between any known Sunday in the past (we could use any Sunday after 1/1/1700; 1/7/1900 is simply the most commonly used Sunday) and the desired date to evaluate, divide that number by the number of days in a week, find the remainder and count that many days from Sunday. For example, today’s date (7/5/2017) is 42,913 from 1/7/1900. When we divide 42,913 by 7, we get 6130 remainder 3; 3 days from Sunday is Wednesday, which is the correct day! The CASE part of the formula is what returns the name of the day of the week; you can see above that the result that corresponds with the value 3 is “Wednesday”.
Variation: Next dayOfWeek
Let’s say your organization classifies all new contacts by the Saturday on or after which they were added. To show this easily, you want to create a formula to display the Saturday after the Created Date (provided that the Contact was not created on a Saturday). Let’s work backwards on this one.
Say the created date is today, July 5, 2017. We want to show Saturday, which is 3 days from today.
Wednesday + 3 = Saturday
(day 3) + 3 = (day 6)
See where we’re headed yet? The variable in the formula will be the number of days to add to the Created Date to show a Saturday; we have already calculated the “day number” above using MOD(); Saturday will be hard-coded as (day) 6. Please note that you’ll need to use the DATEVALUE() function to convert CreatedDate from DateTime to Date.
MOD(DATEVALUE(CreatedDate) – DATE(1900,1,7),7) + x = 6
x = 6 – MOD(DATEVALUE(CreatedDate) – DATE(1900,1,7),7)
So we’ll add x to the Created Date to show the next Saturday: DATEVALUE(CreatedDate)+(6-MOD(DATEVALUE(CreatedDate)-DATE(1900,1,7),7). This works well for cases where the number value of the day of the week is greater than the desired day to show, such as when the record was created Wednesday and we want to show the next Saturday, but if we wanted to show the next Tuesday, we’d end up showing the previous Tuesday (our value for x would be negative). So we’ll add an IF statement to add a week when the desired day of the week has passed in that week: DATEVALUE(CreatedDate)+(6-MOD(DATEVALUE(CreatedDate)-DATE(1900,1,7),7)+IF(6-MOD(DATEVALUE(CreatedDate)-DATE(1900,1,7),7>=6,7,0).
In this case, there is no day in the week after Saturday, so we’re in the clear. But if we classified the contacts by the Sunday of the week they were added, it would be another story. There you have it! It may not be easy on the eyes, but hopefully we’ve helped you to navigate the formula and variation.
Use Case: Login Flow
One potential use case for the Day of the Week formula is the rendering of Login Flows. Our post last week addressed the creation of Login Flows to better guide your users to what they need. What if you need to change the user login experience depending on the day of the week? Now you can do so easily. Simply create a formula in the Flow that determines the “day number” and a decision block that checks that day of the week. If you want to create one path for weekend logins, make the parameters formula = 0 OR formula = 5 OR formula =6. Now you can send special reminders to complete timecards on Fridays, update open opportunities before close for the week, or anything else that can help your users have a better experience.
There are plenty of other great ways to utilize the Day of the Week formula. We hope this post has you thinking of use cases for your own org, and we appreciate you taking the time to read today. Have another topic you’d like to see in the future? Send us a tweet! @BeardforceTyler