0

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", 
18, 
  IF( VALUE(Start_Hour__c) <6, "Saturday (Friday Night)", "Saturday"), 
19, 
  IF( VALUE(Start_Hour__c) <6, "Sunday (Saturday Night)", "Sunday"), 
20, "Monday (Sunday Night)", 
"error")

And Start_Hour__c is

MID(TEXT(DateTime__c), 12, 2)
Improve this question
4
  • 1
    Please include your formula here.
    – Adrian Larson ♦
    Feb 2 '17 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?
    – Fred
    Feb 2 '17 at 6:21
  • Same thing on both views. @Fred
    – denvergreene
    Feb 3 '17 at 2:55
  • Adrian Added the formula.
    – denvergreene
    Feb 3 '17 at 3:02
2

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:

CASE( 
 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, 
 DATE(9999,01,01))

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.

Improve this answer
2
  • 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.
    – denvergreene
    Feb 3 '17 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.
    – denvergreene
    Feb 3 '17 at 5:34

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.