Often utilise system fields such as CreatedDate.

With Date/time fields it’s important to keep in mind time zone considerations in order to avoid problems.

If you’re subtracting two date fields the result is a whole number. If you subtract two date/time fields then the result is a number with a decimal.

Convert a date/time value to date with the DATEVALUE() function. It also can convert a string as below.

DATE(2015, 03, 17)

Conversely you can convert a date to date/time with DATETIMEVALUE() function. It also can convert a string as below.

DATETIMEVALUE("2015-03-17 17:00:00")

You can add a date directly into the formula with the DATE() function. As below:

DATE(2015, 03, 17)

To find the current day as a date us the function TODAY()

To find the current moment as a date/time use NOW()

To take just the day, month, or year from a Date value as a number, use DAY()MONTH() or YEAR() respectively.

A simple way to use dates in formulas is to calculate the number of days between two dates. Be caraeful to only subtract past dates from future dates though, in order to avoid negative result. For example:

TODAY() - DATEVALUE(CreatedDate)

*Here DATEVALUE() was used because CreatedDate is actually a date/time value which is not compatible with a date value?

You can add days to a date too. For example you’d create a date formula field and then in the formula editor use something like this:

TODAY() + 3

CASE() is similar to IF() but it deals with multiple outcomes/results. You can often get the same result with nested if statements but case can be easier to understand, cleaner.

The following formula determines which day of the week it is by using MOD() formula, today’s date, and a known Sunday in the past. Then it uses the cases to determine how many business days ahead there will be. For example, if it’s Saturday there seems to be 1 day plus 3 business days.

CASE(
  MOD(TODAY() - DATE(1900, 1, 7), 7),
  3, TODAY() + 2 + 3,
  4, TODAY() + 2 + 3,
  5, TODAY() + 2 + 3,
  6, TODAY() + 1 + 3,
  TODAY() + 3
)

The following formula calculates the last day of the month by subtracting a day from the first day of the following month (not sure about this one):

IF(
MONTH(Date__c) = 12,
DATE(YEAR(Date__c), 12, 31),
DATE(YEAR(Date__c), MONTH(Date__c) + 1, 1) - 1
)

There are some more sophisticated date formulas here which I don’t quite understand yet but could be useful in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s