Print this page

Date/Time Calculations in Formula Fields, Workflow Field Updates and Validation Rules

Knowledge Article Number 000005091

Learn more about Date/Time calculations in Formula Fields, Workflow Field Updates, and Validation Rules through helpful examples below. 


Common Date/Time Formulas


Example Formula 1 

A Date/Time formula to calculate the time since a record has been created:

    NOW() - CreatedDate

returns a numeric value in the format 99.99XX, depending on the number of decimal places specified. 

Here's how it works: 

The value to the left of the decimal is intuitively the number of days.  The value to the right of the decimal is a representation of the portion of a 24 hour period of the hours and minutes returned by the calculation.

A returned calculation value of 2.25 represents 2 days and 6 hours. 
.25 * 1440 (the number of minutes in a 24 hour period) = 360 minutes
360/60 minutes = 6 hours

Caution Sign Important: Be aware of timezone conversion issues.

  • Within the Salesforce application, dates & date/times are stored and managed in UTC (Universal Time Zone or Greenwich Mean Time).
  • Date and Date/Time field values are converted and displayed in the timezone specified in your user personal information settings. However, in a formula, NO TIMEZONE CONVERSION IS PERFORMED. This means, unless you happen to be in London, your date calculations will be off by the number of hours you are shifted away from GMT (and don't forget about daylight savings time).


Example Formula 2 

If you create a Formula to display a calculated Date/Time Formula value such as:

      TEXT(NOW() - CreatedDate)

the value displayed will be a UTC value and likely inaccurate for your time zone.

Here's how it works: 

You'll want to modify your Formula to adjust the Timezone value.  If you're 7 timezones removed from UTC, you should calculate the decimal value of 7 hours of a 24 hour period:

7 * 60 minutes = 420 minutes

420/1440 minutes in a 24 hour period = 0.2916

The resulting formula modification would be:

TEXT((NOW() - CreatedDate) - 0.2916)

promote demote