Workflow Field Update - set date/time field to NOW() but without seconds precision

Question

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.

Answer 1

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.

DATETIMEVALUE(LEFT(TEXT(NOW()), LEN(TEXT(NOW())) - 3) + "00Z")

Answer 2

I've not heard of a native way of handling this, so a programmatic solution might be best in using a Trigger to handle this instead.

According to Salesforce's Triggers and Order of Execution, you couldn't use this alongside a Workflow because before and after triggers are fired before Workflows and therefore the Date put in by a Workflow wouldn't exist or be old.

3) Executes all before triggers.

7) Executes all after triggers.

10) Executes workflow rules.

You could, in the Trigger, check if that field is empty and, if so, put in the Date/Time with seconds set to 00.

You can get the current DateTime via Apex similar to the following:

Integer y = System.now().year();
Integer mo = System.now().month();
Integer d = System.now().day();
Integer h = System.now().hour();
Integer mi = System.now().minute();

DateTime dt = DateTime.valueOf(y + '-' + mo + '-' + d + ' ' + h + ':' + mi + ':00');

System.debug(dt);

Try it in the Workbench although I appreciate you're using WorkFlows currently, this might not be the best thing you're looking for, but it is a workaround.