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

Here are a couple sample date and time formulas with additional details that will help you build your specific formula.

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

Important: Be aware of time zone 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 time zone specified in your user's personal information settings. However, in a formula, NO TIME ZONE CONVERSION IS PERFORMED. This means, unless you happen to be in London, your date calculations are off by the number of hours you’ve 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 is a UTC value and is likely inaccurate for your time zone.

Here's how it works: 

You will want to modify your formula to adjust the time zone value. If you're 7 timezones removed from UTC, 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((CreatedDate) - 0.2916)

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