0

I have a requirement to convert a date field to date time and the time should always display 00:00 AM. I tried the below formula

DATETIMEVALUE(Text(Created_date__c)&" "& "00:00:00") 

where Created_date__c is a date field.

It returns '12/02/2019 11:00 AM'. As per the requirement, it should display '12/02/2019 00:00 AM'

1

DATETIMEVALUE() will set the time to Greenwich Mean Time (GMT), and then convert it to the time zone of the user viewing the record when it's displayed. See Using Date, Date/Time and Time Values in Formulas in the Salesforce Help for more information.

There is no way to determine a user's time zone in a formula. If all your users are in the same time zone, you can adjust the time zone difference by adding or subtracting the time difference between the users' time zone and GMT to your converted values. However, since time zones can be affected by Daylight Saving Time, and the start and end dates for DST are different each year, this is difficult to manage in a formula. We recommend using Apex for transactions that require converting between Date/Time and Text or Date values.

So, if you meet the criteria of not having to worry about DST and having all your users in the same time zone, you may be able to modify your formula so that the time accommodates the offset to GMT. Otherwise, you're better off using Apex for this.

0

You can convert a Date value to a Date/Time using the DATETIMEVALUE( date ) function. The time will be set to 12:00 a.m. in Greenwich Mean Time (GMT), and then converted to the time zone of the user viewing the record when it’s displayed. For a user located in San Francisco, DATETIMEVALUE( TODAY() ) returns 5:00 p.m. on the previous day (during Daylight Saving Time) rather than 12:00 a.m. of the current day.

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.