# Calculate Days Prior and Return a Date?

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
)
``````

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