I've created a value on a custom object that says the time of day. It should output "Saturday (Friday night)" or "Sunday (Saturday Night)".

The strange thing is when I'm looking at just that record, it will have the correct value. But when I look at that record in a listview, it has the wrong value.

CASE(DAY( DATEVALUE(DateTime__c) ) , 
15, "Wednesday", 
17, "Friday", 
  IF( VALUE(Start_Hour__c) <6, "Saturday (Friday Night)", "Saturday"), 
  IF( VALUE(Start_Hour__c) <6, "Sunday (Saturday Night)", "Sunday"), 
20, "Monday (Sunday Night)", 

And Start_Hour__c is

MID(TEXT(DateTime__c), 12, 2)
  • 1
    Please include your formula here.
    – Adrian Larson
    Feb 2, 2017 at 1:50
  • May have got something to do with logic that makes up the list view. Does it also show differences when you select the 'show all' view?
    – user28311
    Feb 2, 2017 at 6:21
  • Same thing on both views. @Fred Feb 3, 2017 at 2:55
  • Adrian Added the formula. Feb 3, 2017 at 3:02

1 Answer 1


I think this won't work because of time zones and how the value is stored in gmt. I have a hunch that when you look at the record it's using your timezone which is correct but when using the list view it's using GMT which would not be correct for you. You would need to update your formula to account for the GMT time.

Also, if your country uses daylight savings you'll have to change the formula twice a year. Also, you're hardcoding the actual days which will only work for the current month it's not dynamic for each month.

I suggest you try to driving from a fixed date in the past and then use MOD to figure out what day it is such as this:

 MOD(DATEVALUE(Date__c) - DATE(1900, 1, 8), 7), 
 3, DATEVALUE(Date__c) + 7, 
 4, DATEVALUE(Date__c) + 6, 
 5, DATEVALUE(Date__c) + 5, 
 6, DATEVALUE(Date__c) + 4, 
 0, DATEVALUE(Date__c) + 3, 
 1, DATEVALUE(Date__c) + 2, 
 2, DATEVALUE(Date__c) + 1, 

I used to have a formula that calculated when end of day would be for each night. Sorry I can't find it. However, what you would do is take your date time and divide by 1440 or 24 to get the specific unit of time such as minutes in a day then add or subtract to get your timezone offset. then you can say if it's x time that means Saturday night.

  • Ahh this could definitely be it. Weird that the list view would use one timezone while the record itself uses a different timezone. I'll try this soon. Feb 3, 2017 at 4:16
  • I'm marking this one as solved. I ended up using a value that was a difference between the date on the object and a hard coded date like you suggested. Then used that to determine the day of week. I only need it for one event, so it doesn't need to last year round, but your solution there looks really good as well. Feb 3, 2017 at 5:34

You must log in to answer this question.

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