1

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

2
  • Perhaps you should share the formula itself...
    – Adrian Larson
    Dec 7, 2017 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, 2017 at 15:00

1 Answer 1

1

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
  • 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, 2017 at 15:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .