Print this page

How to check if a date is in a given calendar quarter?

Knowledge Article Number 000004173
Description

How to check if a date is in a given calendar quarter?

Resolution

To check if a date is in a given year or month, you can easily use the standard Salesforce YEAR(Date) and MONTH(Date) formula functions.

QUESTION: But how can you check if a date is in a particular quarter?


** SIMPLE QUARTER CHECK **
The following formula code shows a method for checking Opportunity Closed Date is in current quarter:

CASE(MONTH(CloseDate), 1,1, 2,1, 3,1, 4,2, 5,2, 6,2, 7,3, 8,3, 9,3, 10,4, 11,4, 12,4,  98)
=
CASE(MONTH(TODAY()), 1,1, 2,1, 3,1, 4,2, 5,2, 6,2, 7,3, 8,3, 9,3, 10,4, 11,4, 12,4,  99)

The idea is that a single "quarter" number will result from each CASE statement, so if the month is 1, 2 or 3 (Jan, Feb or Mar), then the CASE returns a 1 meaning Q1. If the CASE return value (i.e. quarter number) is the same for CloseDate and TODAY(), then they're each in the same quarter.


** CONFIRMING SAME YEAR **
You may also want to mix in the year to make sure a given date is in an exact quarter and year, for example:

AND
( YEAR(CloseDate) = YEAR(TODAY())
, CASE(MONTH(CloseDate), 1,1, 2,1, 3,1, 4,2, 5,2, 6,2, 7,3, 8,3, 9,3, 10,4, 11,4, 12,4,  98)
  =
  CASE(MONTH(TODAY()), 1,1, 2,1, 3,1, 4,2, 5,2, 6,2, 7,3, 8,3, 9,3, 10,4, 11,4, 12,4,  99)
)


** SHIFTED QUARTERS **
Now for a twist. If one of your quarters begins on February or March rather than January, then the year checking around December and January needs additional intelligence in the formula. This example assumes Q1 begins February.

OR
( AND
  ( CASE(MONTH(CloseDate), 2,1, 3,1, 4,1, 5,2, 6,2, 7,2, 8,3, 9,3, 10,3, 0)
    = CASE(MONTH(TODAY()), 2,1, 3,1, 4,1, 5,2, 6,2, 7,2, 8,3, 9,3, 10,3, 0)
  , YEAR(CloseDate) = YEAR(TODAY())
  )
, CASE(MONTH(CloseDate),11,YEAR(CloseDate),12,YEAR(CloseDate),1,YEAR(CloseDate)-1,0)
  = CASE(MONTH(TODAY()),11,YEAR(TODAY()),12,YEAR(TODAY()),1,YEAR(TODAY())-1,0)
)


** CUSTOM FISCAL QUARTERS **
The above methods will not work for custom fiscal periods which are not based on calendar months but rather specified dates in that may fall anywhere day of the year. For custom fiscal periods, you will need to use exact dates some time into future years to delimit quarter ranges.

Here's an example of a formula for a Validation Rule to confirm Opportunity Close Date is in the current quarter of a custom fiscal year:

IF( CloseDate < DATE(2008, 6, 27), "Unknown-Qx1",
IF( CloseDate <= DATE(2008, 9, 25), "2009-Q1",
IF( CloseDate <= DATE(2008, 12, 25), "2009-Q2",
IF( CloseDate <= DATE(2009, 3, 26), "2009-Q3",
IF( CloseDate <= DATE(2009, 6, 25), "2009-Q4",
IF( CloseDate <= DATE(2009, 9, 24), "2010-Q1",
IF( CloseDate <= DATE(2009, 12, 24), "2010-Q2",
IF( CloseDate <= DATE(2010, 3, 25), "2010-Q3",
IF( CloseDate <= DATE(2010, 6, 24), "2010-Q4",
"Unknown-Qy1")))))))))
<>
IF( TODAY() < DATE(2008, 6, 27), "Unknown-Qx2",
IF( TODAY() <= DATE(2008, 9, 25), "2009-Q1",
IF( TODAY() <= DATE(2008, 12, 25), "2009-Q2",
IF( TODAY() <= DATE(2009, 3, 26), "2009-Q3",
IF( TODAY() <= DATE(2009, 6, 25), "2009-Q4",
IF( TODAY() <= DATE(2009, 9, 24), "2010-Q1",
IF( TODAY() <= DATE(2009, 12, 24), "2010-Q2",
IF( TODAY() <= DATE(2010, 3, 25), "2010-Q3",
IF( TODAY() <= DATE(2010, 6, 24), "2010-Q4",
"Unknown-Qy2")))))))))





promote demote