function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jochen HoscheidJochen Hoscheid 

Error on Challenge: "Troubleshoot a formula and fix a couple of errors"

I tried to fullfill the following challenge:
The following formula, meant to return the last day of the current month, has a couple of errors in it:

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

Create a new formula with the same label, name, and data type that successfully compiles.
- The formula should be of Date type and on the Case object
- The formula should have the name Last Day of Month and the resulting API name Last_Day_of_Month__c
- The formula should return the last day of the current month

I created a new formular field named "Last Date of Month" for the case object. My formular is as follows which has no syntax errors:
IF(MONTH(Today()) = 12,
  DATE(YEAR(Today()), 12, 31 ),
  DATE(YEAR(Today()), MONTH(Today()) + 1, 1) - 1
)

However, I got the following error message when I try to approve the challenge:

Challenge Not yet complete... here's what's wrong:
There was an unexpected error in your org which is preventing this assessment check from completing: System.DmlException: Insert failed. First exception on row 0; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, You can only set a case as escalated if it is high priority and not closed: [IsEscalated]

Has anyone a clue what I did wrong?

Many thanks in advance.
Amit Chaudhary 8Amit Chaudhary 8
Please check below post for same issue
1) https://developer.salesforce.com/forums/?id=906F0000000MLkXIAW

I hope you created one validation (Mark_as_Escalated) on case object same like below post
1) https://developer.salesforce.com/forums/?id=906F0000000MIwBIAW
2) https://trailhead.salesforce.com//en/advanced_formulas/picklist_formulas

Please deactivate the same and try your module once again
 
Jochen HoscheidJochen Hoscheid
Hello Amit,
thank you for the tips. When deactivating the validation rule "Mark_as_Escalated", everything works. I now got
the 500point for the challenge.

I wonder why my problem is related to a validation rule in the case object. I do not see the relation between
the challenge and the validation rule in the case object. Can you help me to understand that?

Appreciate your insights, many thanks in advance.
Amit Chaudhary 8Amit Chaudhary 8
Because validation and formula both are on Case object only. When salesforce validatating your changes they are getting error.
dilip varangantidilip varanganti
1) Create one custom 'Last Day of Month' formula field on case object of type date and enter the below formula.

IF(
  MONTH( today() ) = 12,
  DATE( YEAR( today() ), 12, 31 ),
  DATE( YEAR( today() ), MONTH ( today() ) + 1, 1 ) - 1 
)
Chris Job 8Chris Job 8
Would anyone be able to take this one step forward to show the Last Day of Month in YYYY/MM/DD?

E.g.

2018/04/30
Matheus Augusto Souza 3Matheus Augusto Souza 3
Hi guys.
I don't understand how this formula works. It is possible someone explain this to me? It seems be very easy, but i can't figure out.
SHRIKANT GARANDESHRIKANT GARANDE
Hi Matt,

MONTH( today() ) = 12,                                                       /* Today() checks for current month*/
  DATE( YEAR( today() ), 12, 31 ),                                       /* Here Today() checks  for Current  Year*/
  DATE( YEAR( today() ), MONTH ( today() ) + 1, 1 ) - 1     */ YEAR( today() ) = 2019,
                                                                                                 MONTH ( today() ) + 1 = 9 + 1 = 10
                                                                                                     1             = First Day of next month 
                                                                                                (2019, 10, 1) - 1 = returns Last Day of month(Sept's Last Day) it substracted one Day.                                                                                                   */
                                                                                                 
 
 
Samsudeen AmeenSamsudeen Ameen
DATE(
  YEAR ( TODAY() ),
  MONTH( TODAY() ) + 1, 1 ) - 1

For clarity, as today's date is 04/09/2019 (UK Format). This formula returns the Date value of the first day 1 month from today (01/10/2019) and subtracts 1 day to give you the last Date value of the current month = 30/09/2019. 
Christophe Lereverend 4Christophe Lereverend 4
Its work from this method. 

User-added image
Krishna ShahiKrishna Shahi
Here are 2 Method which worked for me.
Hope this will help you.

METHOD-1
IF(
MONTH(DATEVALUE(NOW())) = 12,
DATE( YEAR( DATEVALUE(NOW()) ), 12, 31 ),
DATE( YEAR( DATEVALUE(NOW()) ), MONTH( DATEVALUE(NOW()) ) + 1,
1
) - 1
)

METHOD-2
IF(
  MONTH( today() ) = 12,
  DATE( YEAR( today() ), 12, 31 ),
  DATE( YEAR( today() ), MONTH ( today() ) + 1, 1 ) - 1 
)

 
Apoorva Jain 28Apoorva Jain 28
Hello,
Try this which worked for me -

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

Hope this will help you .
Kamalanath Annamalai 7Kamalanath Annamalai 7

Today's date is 20 July 2021.
How Formula reads:

1. IF(Current Month is  December THEN {Current year,12,31} OTHERWISE {Curent Year,Current Month+1,First date}-1)

2. IF(July THEN {2021,12,31} OTHERWISE {2021,July+1,First date}-1)

3. {2021,August,1}-1

4. 2021 July 31
Ryan DmelloRyan Dmello
@kamalnath excellent explanation.. thanks very much.. when looking at a nested formula one must not forget how the parameters passed to the logical operators (in this case IF) are processed.