0

I need a list view to display List of records of current quarter and last month of previous quarter. I think I need to write a formula field to calculate current quarter and last month of previous quarter records.

Can anyone suggest me with formula how to write for above condition or any other way to achieve the above scenario?

3

If January starts a quarter the following checkbox formula should be works :

OR(
    /* When the year is similar between the date field and current year */
    AND(
        YEAR(  DateField__c ) = YEAR( TODAY() ),
        OR(
            /* Check if is the date field is on the same quarter of today */
            CEILING( MONTH( DateField__c) / 3 ) = CEILING( MONTH( TODAY() ) / 3 ),
            /* Or if the date field + one month is on the same quarter of today */
            AND (CEILING( MONTH( ADDMONTHS(DateField__c,1) ) / 3 ) = CEILING( MONTH( TODAY() ) / 3 ), MONTH( DateField__c ) < 12)
        )
    ),
    /* And we manage the case of the december of previous year */
    AND(
        YEAR( DateField__c ) = YEAR( TODAY() )-1,
        MONTH( DateField__c) = 12,
        CEILING( MONTH( TODAY() ) / 3 ) = 1 
    )
)

In your list view add this checkbox formula in your filter with "true" value.

| improve this answer | |
  • Hi David Thanks for your help. Actually begin of the first quarter is April as per our client. So is this formula will work if begin of the first quarter is April? – Frank Sep 16 '19 at 19:25
  • Yes the formula should answer to your need. The mandatory thing is not that January is the begin of the 1st quarter but January have to start a quarter. So in your case it's ok because your Q4 starts with January. (I just edited the formula to avoid a problem so think to update your implementation. – David-IT Sep 17 '19 at 7:57
  • Don't forget to mark your answer to solved when you have finished to test this formula. – David-IT Sep 17 '19 at 8:02
  • @chris my solution solved your problem or not ? – David-IT Sep 25 '19 at 12:19
  • Thank you David. – chris Sep 26 '19 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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