1

I have a custom date field called "payment_due_date__c" and a formula field called "reminder_date__c". I want to display a date in the reminder date field equal to 5 business days prior (not including holidays) to the value in the payment due date field. Business days are defined as Monday - Friday

I did some searching and found other solutions that return the number of days, but I want to return a date value instead.

I have this which is close, but not quiet there:

CASE(
 MOD( payment_due_date__c- DATE( 1900, 1, 7 ), 7 ),
  3, payment_due_date__c- 2 - 2,
  4, payment_due_date__c- 2 - 3,
  5, payment_due_date__c- 2 - 4,
  6, payment_due_date__c- 1 -5,
  payment_due_date__c- 5
)
0

It seems like you are only trying to consider weekends in your calculation and not public holidays. If that is the case, then the following formula should do the trick.

CASE( 
    MOD( WEEKDAY(Payment_Due_Date__c), 7 ), 
    0, Payment_Due_Date__c - 5, 
    6, Payment_Due_Date__c - 4, 
    Payment_Due_Date__c - 6 
)

The above should provide the following result:
When Payment Due Date = 1/20/2019, then Reminder Date = 1/14/2019
When Payment Due Date = 1/21/2019, then Reminder Date = 1/15/2019
When Payment Due Date = 1/22/2019, then Reminder Date = 1/16/2019
When Payment Due Date = 1/23/2019, then Reminder Date = 1/17/2019
When Payment Due Date = 1/24/2019, then Reminder Date = 1/18/2019
When Payment Due Date = 1/25/2019, then Reminder Date = 1/21/2019
When Payment Due Date = 1/26/2019, then Reminder Date = 1/21/2019
When Payment Due Date = 1/27/2019, then Reminder Date = 1/21/2019


Notice the last 3 results are the same due to the payment due date being on a weekend.

  • There seems to be a problem @aayush-k if the payment due date is 1/24/2019, 5 prior business days would be 1/17/2019, not 1/18/2019 – user1669296 Jan 4 at 23:24
  • That should be an easy fix. I was assuming by 5 business days, you mean the payment due date will be included in counting the 5 days. You can try the following - CASE( MOD( WEEKDAY(Payment_Due_Date__c), 7 ), 0, Payment_Due_Date__c - 5, 1, Payment_Due_Date__c - 6, Payment_Due_Date__c - 7 ) – Aayush K Jan 4 at 23:34

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.