validation rule to fire on every month last friday in salesforce [closed]

Question

I have a date field called "Next_Visit_Date__c" Now I want to write validation my next visit is only the last friday of each month if I take any date other than last fire on every month validation has to fire. I tried with the below but it is not working

AND(
   WEEKDAY(Next_Appointment_Date) = 6,
   MONTH(Next_Appointment_Date) = MONTH(TODAY()),
   DAY(Next_Appointment_Date) > 23,
   DAY(Next_Appointment_Date) <= 31
)

Can anyone help on this to write a validation rule

Answer 1

Remove Month check condition. It is considering current month only due to we used MONTH(TODAY()).

here is validation conditions:

AND( WEEKDAY( Appointment_date__c ) = 6 ,
DAY( Appointment_date__c ) > 23,
DAY( Appointment_date__c ) <= 31
)

Answer 2

The expression you need, to avoid incorrectly considering both the last and second to last Friday in the month for months with 30 and 31 days, is:

(WEEKDAY(Next_Appointment_Date__c) = 6) &&
 ((DATE(YEAR(ADDMONTHS(Next_Appointment_Date__c, 1)), MONTH(ADDMONTHS(Next_Appointment_Date__c, 1)), 1) -
   Next_Appointment_Date__c) <= 7)

This is only true when Next_Appointment_Date__c is the last Friday of the month (so depending on your validation, you may need to apply a "NOT" around this).

It works by checking that it is a Friday and then finding the first day of the following month then makes sure that the appointment date is within 7 days of this (the last day of the month is counted as an offset of 1 from the first day of the next month).

EDIT

In struck me that this could be even as simple as:

(WEEKDAY(Next_Appointment_Date__c) = 6) &&
 (MONTH(Next_Appointment_Date__c) !=
  MONTH(Next_Appointment_Date__c + 7))

(The latter not tested)

Answer 3

NOT(WEEKDAY(Next_Appointment_Date__c) = 6) && ((DATE(YEAR(ADDMONTHS(Next_Appointment_Date__c, 1)), MONTH(ADDMONTHS(Next_Appointment_Date__c, 1)), 1) - Next_Appointment_Date__c) <= 7)

This is working as expected we need to add the Not condition or else it will not fire.