Formula to extract day of the week from a date field - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Aniqa MoinuddinAniqa Moinuddin 

Formula to extract day of the week from a date field

Does anyone know how I can define a formula that returns Monday/Tuesday/Thursday etc from a date field? The only formula I can think of requires that I define a variable ( Sunday = Day (Date expression) + 7x; where x= integer between 0 and infinity)

Thanks!
Best Answer chosen by Moderator (salesforce.com) 
Sami EllongSami Ellong
Try this:

CASE( MOD( Date__c - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3,
"Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")

All Answers

Sami EllongSami Ellong
Try this:

CASE( MOD( Date__c - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3,
"Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")
This was selected as the best answer
Aniqa MoinuddinAniqa Moinuddin
Works like a charm! Thanks a lot :D
Shikha JaiswalShikha Jaiswal
This is simple and sweet --
DateTime dt = system.today();
String day = dt.format('EEEE'); //day would show expected outcome.
Jamie SmithJamie Smith
I needed to use @Sammi's example with CreatedDate - a DateTime datatype so altered with DATEVALUE(CreatedDate) and figured I'd share for the next person.
 
CASE( MOD( DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")

Thanks @Sammi for the method.
Hamayoun KhanHamayoun Khan
Thanks again @Sammi, I needed to run a workflow only on Friday, and this allowed me to figure out how to do that.
Meta HirschlMeta Hirschl
Thanks so much @sami - worked perfectly. Next problem -- new formula field wasn't showing up to use for report. that's when I found the "Fields Available for Reports". One thing leds to another. thanks to all!
Mike O'NeillMike O'Neill
Thank you @sami
Minhaj_Developer ArifinMinhaj_Developer Arifin
Thank you @Sami and @Jamie!
Rahul GautamRahul Gautam
Works great for me, Thanks a lot @Sammi
Travis GibbonsTravis Gibbons
New to Salesforce.  When I attempt this, the syntax doesn't check out.  Thoughts?  Thanks!
Fraidy StraussFraidy Strauss
Thank you @Sami and @Jamie! Exactly what I was looking for!
Kevin Carl DavidKevin Carl David
UPDATE: As of Spring 2018, there's a built-in function for this: WEEKDAY(). It returns an integer where 1 is Sunday, 2 is Monday, 3 is Tuesday, etc.      - EZ :-)
Annaleece BestAnnaleece Best

@Kevin Carl David - how do we utilise this new WEEKDAY function?

I've tried the above formula and keep getting syntax errors. 

Jim StoltzfusJim Stoltzfus
@Annaleece you might have to combine it with DATEVALUE if the field is a datetime field, like WEEKDAY(DATEVALUE(CreatedDate)) = 1
Gerri WheelerGerri Wheeler
@Kevin Carl David -thank you for the heads up on the new formula.  I used it to render Text in a field like this:

CASE(WEEKDAY(DATEVALUE(CreatedDate)), 
1,"Sunday", 
2,"Monday", 
3,"Tuesday", 
4,"Wednesday", 
5,"Thursday", 
6,"Friday", 
7,"Saturday", 
"")
Krishnan MishraKrishnan Mishra
If you are doing this in Apex, you can try the below: 

Date tdy = Date.today();
System.debug(tdy); // 28-01-2019
Datetime dt = (DateTime)tdy;
String dayOfWeek = dt.format('EEEE');
System.debug(dayOfWeek); // Monday
Jimmy ZambranoJimmy Zambrano
@Kevin Carl David and @Gerri Wheeler and everyone -Thank you your help! This worked for me.

To provide a little background: I needed to create a formula field named "Day of Monitoring" that would render the day of the week from a field named "Date of Monitoring".

Just to walk through the steps of what I did to make this work for me:

1- Created a "Formula" field
2- Selected "Text" for "Formula Return Type"
3- Copied and pasted Gerri Wheeler's formula and customized to fit my needs as follows:

CASE(WEEKDAY(Date_of_Monitoring__c), 
1,"Sunday", 
2,"Monday", 
3,"Tuesday", 
4,"Wednesday", 
5,"Thursday", 
6,"Friday", 
7,"Saturday", 
"")

Thanks again!
Lynnae TaylorLynnae Taylor
Hi everyone - thank you for all of this great information!

I am trying to get the weekday of the Opportunity Close Date, but I keep getting a syntax error. Any tips?
Daymon BoswellDaymon Boswell
Hi Lynnae, 

I just tried this using the standard close date field on the Opportunity and didn't recieve any errors - make sure that your formula type is text when setting up the field. Here is the formula I used: 

CASE( MOD(  CloseDate  - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3,
"Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")
Sagar GoswamiSagar Goswami

When i change the date to 19/1/2020 it doesn't show any results in the below formula:

CASE(
MOD(DATEVALUE( CreatedDate ) - DATE(2020, 19, 1) ,7),
0, "D",
1, "L",
2, "M",
3, "X",
4, "J",
5, "V",
6, "S",
"None"
)

WHY????

Rebekah CampbellRebekah Campbell
I've seen several formulas for excluding weekends, but I'm struggling to create a report which excludes weekends in calculating the number of days since a task was modified. Can anyone help? Thank you in advance!
Joan PrestonJoan Preston
Thanks for the great formula! The report I created groups by rows to get the weekday record counts. The weekdays are listed in order from Friday to Wednesday. Is there a way to re-order the report to start on Monday?