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?

3 Answers 3


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
    Commented Oct 19, 2016 at 9:26

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
    Commented Oct 19, 2016 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. Commented Oct 19, 2016 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
    Commented Oct 20, 2016 at 15:35
  • Did they ever figure this out for you? Commented Oct 27, 2016 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
    Commented Nov 1, 2016 at 12:43

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


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.

You must log in to answer this question.

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