ShowAll Questionssorted byDate Posted
WPCMS

# 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)),
TODAY() ))))))```

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

rt3203

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))

rt3203

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

WPCMS

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?

WPCMS

Any further suggestions on this?

rt3203

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_

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

DATE(
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()

)

WPCMS

This works and is very simple. Thank you.