3

I have two fields EVENT_DATE(type DateTime) and EDate(type Date). I am using a workflow to get Date value from EVENT_DATE and store it in EDate. Workflow formula :- DATEVALUE(EVENT_DATE). The workflow fires at record creation time or anytime record is updated.

however i have noticed that the value for EDate in some of the records is not correct.

Example :2015-05-13T22:47:41.000Z is gives 14-05-2015 using the DATEVALUE formula.

This is causing real trouble for our users.

Is there any way to correct this without having to Update all the the data? Any solution in the form of configuration or code is welcome.

2
  • 1
    What is the user's time zone? Dec 31, 2015 at 4:46
  • Our user base is global. Hence, there is no specific time zone in which this error is confined. Dec 31, 2015 at 5:27

1 Answer 1

5

DATEVALUE() Returns a year, month, day in GMT time value while DATE() returns a year, month, day based on a User's time zone. It would appear that's the cause of your issue. The formula is evaluated based on the context of the running user.

DateTime values are stored in the Salesforce Database in GMT time and they're displayed to a user in the user's local time zone per the time zone settings in their User profile. What YOU see when looking at them is based on YOUR User Profile time zone, not the User who scheduled the Event. Perhaps if you check the event date times in GMT and compare the DATEVALUES, you may discover that they're being converted properly. You may also want to check the User Profile settings where these discrepancies are being reported.

I say this because if someone with the wrong user Time Zone in their profile kicks off the workflow, the conversion to GMT will be made incorrectly and this error will propagate to all users when they view the change.

4
  • Are you saying that when user saved a record at timestamp 2015-05-13T22:47:41.000Z according to local time zone, but according to GMT it was already 14-05-2015, hence, DATEVALUE() returned 14-05-2015? Dec 31, 2015 at 6:04
  • Yes, it could very well be. Some local time zones, the ones EAST of Greenwich, are a negative UTC offset by up to 12 hours while the ones WEST of Greenwich are a plus offset by up to 12 hours, all depending on the time zone. That said, if the StartDateTime is stored in UTC time, all it takes is someone who's profile is an hour or two off what it should be to edit the record to cause the change in StartDateTime to move to the next Day and thus the DATEVALUE to change.
    – crmprogdev
    Dec 31, 2015 at 6:54
  • We are never editing the records, its like history records so its never updated. I have tried editing one affected record and date corrected itself through the same workflow. Dec 31, 2015 at 7:11
  • Okay, then let me give you an example. I live in US Central Time Zone which is UTC + 5. When it's 11PM here, it's already 4AM of the following day in Greenwich. So, the GMT DATEVALUE of any Datetime I had in the system, would be recorded not for today, but instead as tomorrow. The time offset isn't taken into account for the local time zone in which it occurs. If you want that context, you'll want to use DATE.
    – crmprogdev
    Dec 31, 2015 at 13:29

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.