Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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:

enter image description here

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!

share|improve this answer
    
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. –  Robert Sösemann Aug 21 at 10:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.