ShowAll Questionssorted byDate Posted
saran kumar 9

# Unable to complete the Troubleshooting Formula Errors task.....Help Me

please correct this code and help me earn this badge ...have completed all tasks ..except this one ....

IF( MONTH( NOW() ) = 12,
DATE( YEAR( NOW() ), 12, 31 ),
DATE( YEAR( NOW() ), MONTH( NOW() ) + 1, 1) - 1
@Karanraj
Where you stuck? Did you tried in your org? Its pretty straight forward question in the module.
Simply copy and paste the formula in the editor, click "Check Syntax" it will display the exact error message in the formula
saran kumar 9
here is the picture and  see ..please rewrite the code for me so that i can complete the badge ...thank you

InesGarcia
Hi Saran,

Simple:
1. Missing end closing bracket
2. NOW() returns datetime when should be Date, change for TODAY()

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

Yingyong Taechayingyongchai
I correct formula like Ines V Garcia. But it show message "Challenge Not yet complete... here's what's wrong:
The 'Last_Day_of_Month__c' is not reporting the correct last day of the month."
Jacob W. Prince
Try this, hope it helps.

DATE(
YEAR(TODAY()),
MONTH(TODAY()) + 1,

) - 1
Michelle O'Neal
Hi Jacob,
Thanks for your help on this challenge. Your solution worked for me. Can you explain it to me? I'm having an unusually difficult time understanding this one.
Michelle

Jenn Douglass
So I came a up with a different formula for this challenge to find the last day because honestly I thought changing "NOW()" to "TODAY()" was just too simple so I wrote:

DATE(
YEAR( TODAY() ),
MONTH( TODAY() ),
CASE( MONTH( TODAY()) ,
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31))

And it validated with no error, however when I check the challenge it says: "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' under the Escalated field when triggered: [IsEscalated]"

Can someone explain to me why this is not also valid?  Is this challenge simply asking you to "fix" the errors in the formula they wrote when they state Create a new formula with the same label, name, and data type that successfully compiles."
Stephan B.
Hi Jenn,

in a previous trail-unit (Using Picklists in Formulas) you had created a validation rule, which i think is source of your problem.
Please check if you have set some a the previous added fields to "required" or check the other challenge first.

- This will not work correctly, because february has not always 28 days. in leapyears february has 29 days.

Regards Stephan

Pathma Jemmy
Hi Jacob,

Excellent formula but like Michelle I too didn't understand can you pls explain us...
It would be of grt help.

Thanks once again to Jacob...
Remco van der Leest 5

Coming back to the leap year thing - that doesn't matter.

Even my extremely long, very not-thought-through formula seemed to get the trick done:

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

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

Can anyone explain the solution above Ines kindly put please? @Ines V Garcia

Benoit Alegre
Jacob's formula worked just prefectly!
Thanks Jacob!

I also had a hard time understanding this one.
http://www.excel-exercise.com/function/date-time/date-year-month-day/ (http://www.excel-exercise.com/function/date-time/date-year-month-day/" target="_blank)
Krishna Ravi
Hi,

the below formula worked for me:

DATE(
YEAR(  TODAY() ),
MONTH(  TODAY() ),
CASE(
MONTH(  TODAY() ),
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31
)
)

Thanks,
Krishna.
@Michelle O'Neal, Jacob came with the following solution:

DATE(
YEAR(TODAY()),
MONTH(TODAY()) + 1,

) - 1

And you asked to have it explained. Here it goes:

The general Date format is : DATE (year, month, day)
But the challenge requested to find a formula that would show the last day of the current month.
So, because every month has a different number of days, then we can calculate it also as:
"The first of the following month minus 1 day" ===>>> DATE ( current year, current month +1 , 1) - 1 day

YEAR (TODAY()) = the current year
MONTH(TODAY()) = current month

Hope it helps,

Simple !

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

Change NOW() into TODAY()  and use " ) " At the end of the formula

Paweł Szefer
Quick explanation, if someone did not get how it works:
To evaluate the last day of current month one can use DATE function to get the date for the first day of the next month (month(today()+1), and then subtract 1, to get the date of the previous day, hence:
`DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1) - 1`
this could be all, but if it was December, then the DATE function would get 13 as the second argument and return an error. That is why we need to put a limit for next month calculation, hence:
```IF( MONTH ( TODAY() ) = 12,
DATE( YEAR( TODAY() ), 12, 31),
DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1 ) - 1
)```
Shaun Van Weelden

A pretty cool answer I came up with was:

`DATE(YEAR(TODAY()),MOD(MONTH(TODAY())+1,12),1)-1`
Essentially, using the MOD operator to make sure all date ranges were still valid (when month = 13, 13 mod 12 goes back to 1) - Then substracting the date as others have done.
Gatorrr7
The formula below is the only completely correct answer. Everybody else using the CASE function, it will fail in case of a leap year. Correct answer :-
```IF( MONTH ( TODAY() ) = 12,
DATE( YEAR( TODAY() ), 12, 31),
DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1 ) - 1
)```

InesGarcia
`DATE(YEAR(ADDMONTHS(TODAY(),1)), MONTH(ADDMONTHS(TODAY(),1)), 1) - 1`