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

Here are sample date and time formulas with additional details to help you build your specific formula.

Common Date/Time Formulas

Example Formula 1

A Date/Time formula to calculate the time since a record was created: NOW() - CreatedDate
Returns a numeric value in the format 99.99XX, depending on the number of decimal places specified.

How it works:

The value to the left of the decimal is the number of days. The value to the right of the decimal represents the portion of a 24-hour period corresponding to 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

Important: Be aware of time zone conversion issues.

• Within Salesforce, dates and 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 time zone specified in your user's personal information settings. However, in a formula, no time zone conversion is performed. Unless you are in the UTC time zone, your date calculations will be off by the number of hours you have shifted away from GMT (including daylight savings adjustments).

Example Formula 2

If you create a formula to display a calculated Date/Time formula value such as: TEXT(NOW() - CreatedDate)

The value displayed is a UTC value and is likely inaccurate for your local time zone.

How it works:

You need to modify your formula to adjust for the time zone offset. If you are 7 time zones removed from UTC, calculate the decimal value of 7 hours within a 24-hour period:

• 7 × 60 minutes = 420 minutes
• 420 ÷ 1440 minutes in a 24-hour period = 0.2916

The resulting formula modification is: TEXT((CreatedDate) - 0.2916)

To review or ask about other date and time formulas, visit the Salesforce Trailblazer Community topic on #Formulas — Last Modified Date more than 24 hours ago.