I need to update a date/time field when a picklist "Status__c" is changed. If the user does not provide their own value in the date/time field when changing the "Status__c" field, the Workflow Rule will insert "NOW()" as the value. For other reasons, I cannot use a validation rule to require a value in the date/time field. So, either the user enters a value or the WF field update sets it to NOW().
The problem is the NOW() function returns a GMT value (displayed in the running user's timezone) that includes the precision of the seconds such as YYYY-MM-dd HH:MM:SS where the SS represents 0-60 seconds for that precise moment.
I need the field update to set the date/time to NOW() but with the seconds value as "00" rather than the precise seconds value from the NOW() function.
It seems there aren't any native, declarative "Time" functions available in the Formula editor to manipulate the date/time value as needed.
Interestingly, in Salesforce when a user edits a date/time field using the standard record detail page by clicking the date/time link to the right of the field, the date/time value is stored without the "seconds" precision. This makes validation rules very tricky when comparing user entered date/time values versus those values updated by the NOW() function.
Best Answer
Steve,
You should be able to do this all declaratively using the the DATETIMEVALUE function along with TEXT, LEFT and LEN functions to manipulate the DateTime expression to "round down" your precision.
Pease see the below formula which will convert time to a GMT formatted expression string, then replace the seconds with 00.