Nov 29, 2012

Formula Example - Days Until Quarter's End

Today I bring you two ways to accomplish the same thing; display the number of calendar days until the end of the quarter. You probably wouldn't use these formulas to create a field (why show the number of days on every record?), but might use on a Visualforce page or email template. You may even modify them to enhance a validation or workflow rule.

The first way uses a series of nested IF() functions whereas the second way demonstrates how to arrive at that number using a CASE() function. The CASE way has a small work around in it, explained below.

Using nested IF functions:

Typically a CASE function follows this pattern: CASE(expression,​value1, result1, value2,​ result2,...,​ else_result) which leaves very little room for additional criteria. Expression is usually the field you will look at the value of, valueX will be the value to look for, and resultX will be the text to display if valueX and resultX match.

The issue we have is valueX will be any date between a given quarters start and end dates (displayed within the AND()s below). Nothing prevents us from giving an expression of "1" (aka TRUE) and then providing the values as IF statements that return 1 or 0. We just need to ensure that the datatypes compared match and the criteria doesn't overlap as the first to return 1 or TRUE ends our CASE function.

Using the Case function w/ workaround: