Formula Ninja to the rescue - Calculate Last Day date of period

Question

In my CustomObject__c I define a time period by those 3 picklist fields:

1. pkl_PeriodType__c { Year, Quarter, Month }
2. pkl_Year__c { 2011, ...,2022 }
3. pkl_Period__c { January, ..., December, Q1, Q2, Q3, Q4 }

I need to know on a Database level if such a period lies in the past. This would basically be the same as checking if the last day of such a period is smaller than System.today().

It took me 10min to calculate that in Apex, ...but I miserably failed to solve this in a formula :-(

All you formula ninjas out there - please help!

Answer 1

I will try :) So it took me 30 mins to create and test this formula (the only "bottleneck" here is that this formula not respect a leap year):

First of all i've created a new formula field of type checkbox for the better look on the page layout:

IF(TEXT(PeriodType__c) = "Year",    TODAY() > DATE(VALUE(PeriodYear__c),12,31), 
IF(TEXT(PeriodType__c) = "Quarter", TODAY() > DATE(VALUE(PeriodYear__c),CASE(TEXT(Period__c), "Q1",03, "Q2",06, "Q3",09, "Q4",12, 03), CASE(TEXT(Period__c), "Q1",31, "Q2",30, "Q3",30, "Q4",31, 30)),
IF(TEXT(PeriodType__c) = "Month",   TODAY() > DATE(VALUE(PeriodYear__c), VALUE(TEXT(Period__c)), CASE(TEXT(Period__c), "01",31, "02",28, "03",31, "04",30, "05",31, "06",30, "07",31, "08",31, "09",30, "10",31, "11",30, "12",31, 30)), FALSE)))

This formula checks three cases:

1. Year: here i'll take just a year variable. The end of each year is known (XXXX,12,31)
2. Quarter: here we need to get a month number depending on the quarter given
   "Q1" > March > 03
   "Q2" > June > 06
   and so on ....
   The end of each month is known aswell
3. Month: this one is more tricky. We need to calculate an end value for the given month (sorry, don't have much time for the leap year calculation, that's why permanently 28 days in februar)

Please note, i used following fields for the formula:
Period__c: Picklist with a values 01,02...12 for year and Q1,...Q4 for quarters
PeriodType__c: Picklist
PeriodYear__c: Text field (2011,...2XXX)

I hope this helps!