0

I am getting troubles with the following formula, I am trying to avoid the date 29 -2. The error I am getting is: value not of required type: sfdc.formula.FormulaEvaluationException: Month or Day out of range in DATE() function

It appears when my Service date is 1/3/2018 and if the next Billing Frecuency is Annual, the result it should be 28/2/2018

CASE(BillingFrequency__c,
"Annual",IF(AND(floor(month(Servicedate))= 3, floor(DAY(Servicedate))= 1), 
  DATE((year(Servicedate)+1),2,28),
 DATE((year(Servicedate)+1),
floor(month(Servicedate)),floor(DAY(Servicedate)-1))) ,

"Quarterly",IF(AND(floor(month(Servicedate))= 12, floor(DAY(Servicedate))= 1 ), 
 DATE((year(Servicedate)),
floor(2),floor(28)),
 DATE((year(Servicedate)),
floor(month(Servicedate)+3),floor(DAY(Servicedate)-1))),

"Montlhy",IF(OR(AND(floor(month(Servicedate))= 1, floor(DAY(Servicedate))= 30 ),AND(floor(month(Servicedate))= 1, floor(DAY(Servicedate))= 31)), 
 DATE((year(Servicedate)),
floor(month(Servicedate)+1),floor(DAY(Servicedate)-2)),
 DATE((year(Servicedate)),
floor(month(Servicedate)+1),floor(DAY(Servicedate)-1))),
Servicedate)

Thanks in advance

2
  • Did you check if the date format you are using is MM/DD/YYYY? I think you are checking for date format as DD/MM/YYYY.
    – user36778
    Feb 6 '18 at 11:28
  • Unfortunately is no that case because for the rest of scenarios is working the format YYYY/MM/DD.
    – l13
    Feb 6 '18 at 11:51
1

If the service date is on the first day of the month, using DAY(Servicedate)-1 will try to create a date on the zero'th day of the month. I think this will result in the error that you are getting.

1
  • Thank you, Sander. It is absolutely the point, I am already working to avoid this GAP
    – l13
    Feb 7 '18 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.