2

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'

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
0
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.

Improve this answer
0

DATETIMEVALUE( text( DateField )+' '+'21:00:00') ---FieldValue date with specific date & time as 4 PM EST OR DATETIMEVALUE( text( Now() )+' '+'21:00:00') ----Today date with 4 PM EST OR DATETIMEVALUE(Now() + ' ' + '00:00:00') ---- today date with specific time OR DATETIMEVALUE(Now()) ---- today date with 12:00 time

Improve this answer
1
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Dec 30, 2022 at 16:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .