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?

Improve this question

1 Answer 1

Reset to default
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
5
  • 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
    Commented Sep 16, 2019 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
    Commented Sep 17, 2019 at 7:57
  • Don't forget to mark your answer to solved when you have finished to test this formula.
    – David-IT
    Commented Sep 17, 2019 at 8:02
  • @chris my solution solved your problem or not ?
    – David-IT
    Commented Sep 25, 2019 at 12:19
  • Thank you David.
    – chris
    Commented Sep 26, 2019 at 14:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .