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 ArifinMinhaj 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", 
"")