Not sure if this is a bug or if I am doing something wrong. In my sandbox when I query a formula field I am not getting a value returned. When I look at the record page I can see that the formula is working. The formula field is "Created_Date_Day_of_Week__c". Any ideas?

Formula Field: The formula field is set to Text.

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

Record Page: enter image description here

SOQL Query: enter image description here

  • Perhaps you should share the formula itself...
    – Adrian Larson
    Dec 7 '17 at 14:45
  • 1
    I've seen this before; the UI is more tolerant of date calcs than soql or reports. I'll bet if you run a report on this object, the Created Date - Day of Week will come back as #Error. Adrian is right, need to see formula.
    – cropredy
    Dec 7 '17 at 15:00

After doing some random searching I came across a success community page with this version of the formula. This one works. Don't really know why.

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

Thanks @cropredy for letting me know that the UI and SOQL behave differently.

  • 1
    The most notable difference is that your fallback value is "". If you change that in your original formula does it then work? Starting on Saturday instead of Sunday shouldn't make a difference...
    – Adrian Larson
    Dec 7 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.