Timezone issue when calculating dates

edited September 3, 2019 in Questions

We have a UI only fromula field that is attempting to display a date one day prior to a given date:

YEAR({{Week_End__c}} - (1000*24*60*60))+'-'+
IF(LEN(MONTH({{Week_End__c}} - (1000*24*60*60)))=2,MONTH({{Week_End__c}} - (1000*24*60*60)),'0'+MONTH({{Week_End__c}} - (1000*24*60*60)))+'-'+
IF(LEN(DAY({{Week_End__c}} - (1000*24*60*60)))=2,DAY({{Week_End__c}} - (1000*24*60*60)),'0'+DAY({{Week_End__c}} - (1000*24*60*60)))

So when the Week_End__c is 5/11/2018, it should display 5/10/2018. This works as intended when the user's computer is set to the EST. However, for every other US time zone it calculates for 2 previous days, so it's showing as 5/9/2018. We have a field like this for the 6 prior days from Week_End__c, so there is an off by 1 error for all of them. 

Our org default is EST, so I'm assuming the discrepancy there is the problem. I've been playing around with those modifying values but nothing seems to be working that doesn't throw the dates off for the other timezones as well.


  • edited September 3, 2019
    I think you might save yourself a lot of grief by adding a formula field to your object:

    Day_Before__c as "Week_End__C - 1"

  • Luzie BaumgartLuzie Baumgart 🛠️ 
    edited May 23, 2018
    Hi Joe, was Mike's tip helpful for you?
  • edited May 23, 2018
    It was actually. I just tried it and this way is a lot simpler, thanks.

    One of the things I tried in that formula was replacing those YEAR(), MONTH(), and DAY() functions with the YEAR_UTC() functions. But I didn't see one available for DAY(). There is a UTC version for all of the other date functions like that, which I think would bypass the timezone issue I was having. Is there a reason the DAY_UTC() function is missing?
  • Luzie BaumgartLuzie Baumgart 🛠️ 
    edited May 23, 2018
    Mike, thank you for sharing your knowledge!
    Happy to hear that it was helpful.

    Joe, do you know the list of functions in our documentation?

    You will find a function called “DAY_OF_WEEK_UTC”, is that what you are looking for?

    One more general question, did you double check the selected timezone in the user settings?
  • edited May 23, 2018
    That was the document I was working off of. DAY_UTC isn't there. DAY_OF_WEEK is a different function than DAY, so that won't work either.

    There is YEAR_UTC, MONTH_UTC, HOUR_UTC, MINUTE_UTC, and SECOND_UTC. It's weird that DAY_UTC is missing.

    And I could just set my timezone to EST. But If I am physically in MST I don't want to have to change my timezone just so my skuid pages will work.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!