1

I have a formula field that is showing inconsistent behaviour between detail page layout and list view, report

This is the formula field of datatype DATE

DATEVALUE( DateTime_Field__c )

If DateTime_Field__c has the value between midnight and 1 AM BST (British Summer Time = GMT + 1) , for example 2016/10/15 00:30, the formula field displays 15th Oct in detail page layouts and 14th Oct in list views and reports.

I am on UK time (GMT+1 between Oct and March and GMT otherwise)

Is this expected behaviour? Has anyone encountered this before?

I can understand that most formulae don't take into account Daylight Saving Time, but should it not display the same value in both list view and detail page layout?

0

If DateTime_Field__c is a field of Type DATE then do not use DATEVALUE on it like you mentioned

DATEVALUE( DateTime_Field__c )

Use the DATEVALUE( date/time ) function to return the Date value of a Date/Time. Otherwise i do not think there is any other issue. Hope this will help.

  • DateTime_Field__c is of datatype DATETIME. The problem is that the formula is displaying two different values for the same record in list view and detail page layout under the conditions I've detailed in the original post. – joe Oct 19 '16 at 9:26
0

In formulas timezone conversion is not taken into account. This poorly worded article kind of highlights it: https://help.salesforce.com/apex/HTViewSolution?id=000005091&language=en_US and this stack exchange article explains it here: GMT Timezone in formula fields?

But the basic idea is with it's midnight to 1 the value stored in utc is still the day before, the 14th in your example, once it goes to 1:01 AM it's the 1th in UTC. So you'll have to account for the time difference by doing some math to calculate the off hour. Which while date calculations are super fun you'll also have to change it twice a year when summer time and spring time change it. Unless it's like the US when it's the 4th sunday of the 3rd month then you can parse it out..

A better question for you is why do you need to convert the datetime to a date anyway? What are you getting to that you don't already have? Because if you don't do your formula then you won't have to worry about updating the value and it gets converted for your automatically and correctly.

  • Dan, thank you for your response. This snippet is just the problematic portion from a much larger formula field that I am using. I understand that formula fields do not take into account time zones and I can handle that in the formlua. My problem is that Salesforce seems to take into account timezone in the detail page layout and does not take it into account in list views for the above formula. As a result, the same field is displaying two different values in listview and detail page layout. – joe Oct 19 '16 at 9:21
  • Looking through the documentation it looks like that it should have been not taking it into account at all so the detail page should not have worked. I would log a case with sfdc support and see what they say as to why it's different.. If you get a answer from them put it on here so we'll all know for the future. – Dan Wooding Oct 19 '16 at 15:05
  • Thanks Dan. I have raised a case and spent 1 hour proving to the agent that the bug exists. They are yet to get back. Will post an update once they do. – joe Oct 20 '16 at 15:35
  • Did they ever figure this out for you? – Dan Wooding Oct 27 '16 at 20:45
  • Yes, this was closed as a known issue. It impacts not only DST but also users who aren't on GMT. I've posted an answer with the link to the known issue – joe Nov 1 '16 at 12:43
0

Salesforce support has come back that this is a known issue with no fix yet.

https://success.salesforce.com/issues_view?id=a1p30000000T5cYAAS

DATEVALUE formula is not handling user's timezone correctly in list views, reports and related lists

The workaround suggested is View the value from the record detail page instead of the list view. which isn't of much help.

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.