Timezone issue when calculating dates


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}} - (1000246060))+‘-’+
IF(LEN(MONTH({{Week_End__c}} - (1000
246060)))=2,MONTH({{Week_End__c}} - (1000246060)),‘0’+MONTH({{Week_End__c}} - (1000246060)))+‘-’+
IF(LEN(DAY({{Week_End__c}} - (1000246060)))=2,DAY({{Week_End__c}} - (1000246060)),‘0’+DAY({{Week_End__c}} - (10002460*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.

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”

Hi Joe, was Mike’s tip helpful for you?

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?

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?

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.