Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
WPCMSWPCMS 

Reducing a Formula Size

I am trying to reduce the size of my formula as it keeps giving me a compile error. How do I do this? Is it the formula syntax or the actual field size.

 

 

if(MONTH( Date_Received__c)=1,(DATE(YEAR(Date_Received__c), MONTH(Date_Received__c),31)),
if(MONTH( Date_Received__c)=2,(DATE(YEAR(Date_Received__c), MONTH(Date_Received__c),28)), 
if(MONTH( Date_Received__c)=3,(DATE(YEAR(Date_Received__c), MONTH(Date_Received__c),31)), 
if(MONTH( Date_Received__c)=4,(DATE(YEAR(Date_Received__c), MONTH(Date_Received__c),30)), 
if(MONTH( Date_Received__c)=5,(DATE(YEAR(Date_Received__c), MONTH(Date_Received__c),31)), 
if(MONTH( Date_Received__c)=6,(DATE(YEAR(Date_Received__c), MONTH(Date_Received__c),30)), 
 TODAY() ))))))

 

 

I can only get up to June and I need the whole year in here.

 

Thank you in advance

Best Answer chosen by Admin (Salesforce Developers) 
rt3203rt3203

Sorry I thought that you were trying to calculate the number of days in month of your date field, a number value.

Your trying to calculate the last calendar day of the month, for the month of your date field, right?

If so try this:

IF( MONTH( Date_Received__c ) = 12,
(DATE( (YEAR( Date_Received__c ) +1) , 1, 1) -1),
(DATE( YEAR( Date_Received__c ) , (MONTH( Date_Received__c ) + 1), 1) -1))

All Answers

rt3203rt3203

Try the case function.

 

CASE(MONTH( Date_Received__c ), 1, 31, 2, 28, 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31, 1)

 

Cheers
WPCMSWPCMS

I typed exactly what you did and it gave me an error

 

Error: Formula result is data type (Number), incompatible with expected data type (Date).

 

I am assuming it is looking for the month and the year. How do I add that on to the CASE statement?

 

Thank you in advance
WPCMSWPCMS

Any further suggestions on this?
rt3203rt3203

Sorry I thought that you were trying to calculate the number of days in month of your date field, a number value.

Your trying to calculate the last calendar day of the month, for the month of your date field, right?

If so try this:

IF( MONTH( Date_Received__c ) = 12,
(DATE( (YEAR( Date_Received__c ) +1) , 1, 1) -1),
(DATE( YEAR( Date_Received__c ) , (MONTH( Date_Received__c ) + 1), 1) -1))

This was selected as the best answer
Lori_Lori_

Try this

- Check if the month is between 1 and 12

- If it is, get the year from Received_Dt__c, the month from Received_Dt__c, and the calculated last day of the month

- If it's not, put in today's date

 

If(AND(MONTH(Received_Dt__c)>0,MONTH(Received_Dt__c)<13),

 

    DATE(
    YEAR(Received_Dt__c), 
    MONTH(Received_Dt__c),
    Case (MONTH(Received_Dt__c), 
        1,31,
        2,28,
        3,31,
        4,30,
        5,31,
        6,30,
        7,31,
        8,31,
        9,30,
        10,31,
        11,30,
        12,31,
        0) ),


    today()

 

)
WPCMSWPCMS

This works and is very simple. Thank you.