function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
michelle emanuel 59michelle emanuel 59 

Using Date and Date/Time Formulas

Challenge Not yet complete... here's what's wrong:
The 'Day_of_the_Week__c' is not reporting the correct day of the week (Monday, Tuesday, etc.)
I created the field on the Contact object  per the requirements and created the formula to return a text field with the day of the for "today". I tested my formula and it works but it does not pass the check challenge. The formula I am using is: 
CASE(MOD(Today()- DATE(1900,1,7), 7), 0, 'Sunday', 1, 'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Friday','error')


 
Paul_BoikoPaul_Boiko
It probably because you have Friday twice in your formula for 5 and 6.
michelle emanuel 59michelle emanuel 59
No that did not make a difference. The formula works just fine.
Shalom RubdiShalom Rubdi
I'm getting the same error - but the formula appears to be calculating correctly:
DayOfWeek Screencap
Paul_BoikoPaul_Boiko
That's true. I tried myself and got the same error..
Paul_BoikoPaul_Boiko
Guys, I was able to pass this trailhead if my formula returns Friday for today even though it's still Thursday. I guess Salesforce validates formula for UTC time zone and it's already Friday in UTC. 
This is formula that worked for me (I know it's incorrect) just now. 
CASE(MOD( Today() - DATE(1900, 1, 6), 7), 6, "Saturday", 0, "Sunday", 1,"Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5,"Friday","")
Shalom RubdiShalom Rubdi
Super weird but the challenge passed!
Ryan William SchorrRyan William Schorr
Make sure that you are supplying a proper "else_result" value for CASE(). The CASE() method uses the following syntax: 
CASE(expression, case1, result1, case2, result2, ... , else_result)
For this challenge, you must supply an expression to be evaluated, which is the MOD() method that subtracts today's date from a known day of the week in the past (ex. January 1, 1900 is a Monday) and divides by 7 to return the remainder. Using January 1, 1900, this would look like: 
MOD(Today() - DATE(1900, 1, 1), 7)
You then supply as many case and result pairs as you require. We are creating a formula that returns a text string with the current day of the week, so our instinct is to evaluate 7 pairs; however, as CASE() requires an "else_result" argument at the end, you only need to define 6: 
  1, "Tuesday",
  2, "Wednesday",
  3, "Thursday",
  4, "Friday",
  5, "Saturday",
  6, "Sunday",
You can then use the only day of the week that you did not define as the "else_result": 
"Monday"
Michelle's formula failed because "error" is not a day of the week. In Paul's case, the formula worked because he defined all 7 days and included a null string ("") as the "else_result". This is acceptable too but is a slightly different approach from the one taught in the lesson.


 
Tim Andrews 72Tim Andrews 72
This is what just worked for me, applying what @Ryan William Schorr described above: 

CASE(MOD( Today() - DATE(1900, 1, 1), 7), 1,"Tuesday", 2,"Wednesday", 3,"Thursday", 4,"Friday", 5,"Saturday", 6,"Sunday", "Monday")
Sarah MounaSarah Mouna
It's not the formula its the time setting in your trailhead instance.  change the company and your user time zone to (GMT+00:00) Greenwich Mean Time (Europe/Dublin) so that the test can be validated correctly.  it was giving me the same error untill i changed the time zones.
Venkat Surendra BezawadaVenkat Surendra Bezawada
CASE(MOD(Today()- DATE(1900,1,7), 7), 0, 'Sunday', 1, 'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday','error')
 
ShamalShamal
This worked for me,
CASE(MOD( Today() - DATE(1900, 1, 6), 7), 6, "Saturday", 0, "Sunday", 1,"Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5,"Friday","")
Frank Shapiro 8Frank Shapiro 8
@Sarah Mouna - That solved it for me. i was tearing my hair out. Thanks.
Kumar Gaurav 4Kumar Gaurav 4
@Ryan - Formula is not an issue, issue is related to timezone. 
@Sarah thanks for identifying this.
Ryan William SchorrRyan William Schorr
The problem everyone is having with this challenge is not that you have the incorrect time zone setting in your personal org, it's that Trailhead is checking your answer against the answer they have stored. You are calculating the current day of the week in your time zone; they are calculating the current day of the week in a selected time zone, which appears to be either Greenwich Mean Time (GMT+00:00) or one hour west (GMT-01:00). Changing the time zone is a workaround that allows you to technically pass the challenge but does not necessarily mean that you completed it correctly.

To understand what I mean, let's say you're on the east coast of the United States in Eastern Standard Time (GMT-05:00). You know today is Friday. You've determined a known Friday in the past (January 5, 1900). Your formula correctly calculates that when today's date minus January 5, 1900 results in a remainder of 0, then today is Friday and it produces a text string that says so. The challenge is to create a formula that returns the current day of the week and you appear to have done just that, but when you click Check Challenge it says that your formula is not reporting the correct day of the week! How is that possible?

The problem is that you're working late at night. It's 10 PM on Friday in EST. But in GMT, it's 3 AM, Saturday. Trailhead is checking your "day of the week" string against a string it generates using a known correct formula in a "universal" time zone in Europe. So to Trailhead, today is Saturday, and you're wrong, even though your formula is right.

Though it's not the ideal way to pass the challenge, you can get around this issue by changing your time zone to the one Trailhead is checking from. For those looking to change the time zone, go to [Your name] > My Settings > Personal > Language & Time Zone and change the Time Zone pick list to (GMT +00:00) Greenwich Mean Time (GMT). Again, you shouldn't have this issue during certain times of the day, so you may not need to do this.

It would be better if Trailhead was able to check your personal time zone setting and calculate the "correct answer" string based on that, but I'm not sure this is possible. I'll contact the developers and see what they think.
Kumar Gaurav 4Kumar Gaurav 4
Perfectly make sense what you said. Thanks
Meekle MatterMeekle Matter
@Ryan William Schorr
   Thank you for your comment and explanation.  It gave the whole lesson meaning to me.  I felt it was lacking in defining what they were refering to and you cleared up.  Thank you for taking the time to write your response.
Luiz Rafael MarcondesLuiz Rafael Marcondes
I'm in Brazil GMT-3 and it works for me

CASE(MOD( Today() - DATE(1900, 1, 1), 7), 1,"Tuesday", 2,"Wednesday", 3,"Thursday", 4,"Friday", 5,"Saturday", 6,"Sunday", "Monday")
Tiffany VasquezTiffany Vasquez
Why are subtracting any days?

CASE(MOD(DAY(TODAY()),7),
1, "Saturday",
2, "Sunday",
3, "Monday",
4, "Tuesday",
5, "Wednesday",
6, "Thursday",
"Friday"
)