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

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 :-(

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!

• Thanks for your effort. Thats much more I could get. But.. my Month are as Month names 01 -> January. This will make the formula bigger than the allowed 5000 chars. Another problem is that not every year has a 28 day february. Aug 21, 2014 at 10:17