3

I have an existing validation Rule:

AND(
  OR(Allocated_Date__c <= TODAY(),
     Allocated_Date__c = TODAY()+1
  ),
RecordType.DeveloperName = 'Control_Visit', 
ISPICKVAL(Status , 'Complete')   
)

This works currently and ensures records aren't saved if the allocated date is in the past or today +1. However I would like to adjust it so the +1 only accounts for business days and not weekends. For example, the validation if ran today allows for allocated days of Monday when it should only accept dates from tuesday onwards. Naturally the other pieces of validation apply too.

i've been reading about using Case, Mod and comparing date values like this:

How can i count only weekdays in this formula?

and

Data Validation: only count working days? Today() + 20 working days

Unfortunately I couldn't figure it out :(

1

I would say this is a great use case for a Hierarchy Custom Setting and a Schedulable. Just store the next business day.

public with sharing class BusinessDaysJob implements Schedulable
{
    public void execute(SchedulableContext context) { calculate(); }
    public static void calculate()
    {
        BusinessDays__c setting = BusinessDays__c.getOrgDefaults();
        setting.NextBusinessDay__c = getNextBusinessDay();
        upsert setting;
    }
    public static Date getNextBusinessDay()
    {
        Datetime pointer = Datetime.now().addDays(1);
        if (!BusinessHours.isWithin(defaultHours.Id, pointer))
            pointer = BusinessHours.nextStartDate(defaultHours.Id, pointer);
        return pointer.date();
    }
    static BusinessHours defaultHours
    {
        get
        {
            if (defaultHours == null)
                defaultHours = [SELECT Id FROM BusinessHours WHERE IsDefault = true];
            return defaultHours;
        }
        private set;
    }
}

Now in your formula you can compare against this value:

IF(SomeField__c = $Setup.BusinessDays__c.NextBusinessDay__c)

Make sure you schedule the job to run daily!

  • I think I follow, and we can manually set the date if we had to avoid something specific. Theoretically can it be extended to include holidays - i am not an expert an apex by a long shot but I appreciate such a comprehensive answer! – n34_panda Oct 13 '17 at 17:07
  • 1
    That's how the BusinessHours class works. isWithin would be false on a Holiday, and nextStartDate will always return you a value that is within your business hours. – Adrian Larson Oct 13 '17 at 17:22
  • Thanks Adrian, I've just implemented and tested and it works a treat. Tiny change needed on line 6 (missing __c) but apart from that easy to follow and I've tested with changing business hours and holidays. Many thanks. – n34_panda Oct 13 '17 at 22:03
1

This should work, it will add 2 and 1 extra days for saturday and sunday, so they will count till monday

AND(
    Allocated_Date__c <= TODAY()
    + CASE(MOD(today()-DATE(1900,1,6),7), 0,3, 1,2, 2,1, 3,1, 4,1, 5,1, 6,1),
    RecordType.DeveloperName = 'Control_Visit', 
    ISPICKVAL(Status , 'Complete')   
)
  • 1
    What about holidays? What if it's a company that doesn't work on Mondays? – Adrian Larson Oct 13 '17 at 16:15
  • @AdrianLarson you are right, I was just trying to explain how the given links could be used. So if you don't need special holiday validation, this might be enough. – Basti Oct 13 '17 at 16:19
  • Thanks. holidays are definately worth including but I wasn't far off in my tests. Thanks for the reply – n34_panda Oct 13 '17 at 17:09

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.