This post is a quick tip to find the day of the week of a Salesforce Date or DateTime. There is no “DayOfWeek” built in Salesforce function, so we have to create a custom formula field to do the work.
Since there are always 7 days in a week, we can use ‘date math’ to figure out the weekday of a specific date as long as we know the weekday of some other fixed date. By subtracting the 2 dates, and dividing by 7 (number of days in a week), the number of “extra” days will be the “day offset’ from Sunday.
For this example, I’ll use Sunday, January 6, 2013 as the “known day of week”
To create a custom field that always shows the current day of the week, we’ll use the TODAY() function (assume TODAY() is March 6, 2013)
To get the number of days between TODAY() and the ‘known day of week’ we’ll subtract the 2 dates
TODAY() - DATE(2013,1,6)
this returns 59.
Dividing the number of days between the dates by 7 will give us the number of full weeks (8), and using the MOD() function on the division will tell us how many days of a partial week are left (3).
MOD(TODAY() - DATE(2013, 1, 6),7)
this returns 3.
We can put this formula into a CASE() function to return the day of the week.
CASE( MOD(TODAY() - DATE(2013, 1, 6),7), 0, 'Sunday', 1, 'Monday', 2,
'Tuesday', 3, 'Wednesday', 4, 'Thursday', 5, 'Friday', 'Saturday')
You can learn about all the functions available in custom field formulas from the custom field formula edit screen. Click on a function in the list to see a short description, or follow the ‘Help on this function’ link.