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
    Commented Feb 6, 2018 at 11:28
  • Unfortunately is no that case because for the rest of scenarios is working the format YYYY/MM/DD.
    – l13
    Commented Feb 6, 2018 at 11:51

1 Answer 1

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
    Commented Feb 7, 2018 at 13:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .