Ask Search:
Jaclyn FigueroaJaclyn Figueroa 

Formula field: Due Date MINUS 7 business days

Hi, 

We have a field on our case object called Due Date. This field is manually populated by our users. I also created a formula field to calculate a Reminder Due Date. This Reminder Due Date takes the Due Date and SUBTRACTS 7 business days:

(Due_Date__c) - 
IF(MOD(TODAY() - DATE(1900, 1, 7), 7) <= 3, (7+2), 
IF(MOD(TODAY() - DATE(1900, 1, 7), 7) = 6, (7+1), 7) 
)

The issue I am running into is that sometimes the Reminder Due Date falls on a Saturday or Sunday and I am wondering if this is the case, then could the field formulate the day to fall on a weekday. For instance, if the Due Date is 03/13/2018 then the Reminder Due Date is 03/04/2018 (Sunday), but I am wanting to know if I can have the formula make that Reminder Due Date fall on 03/02/2018 (Friday).

Thanks for any help!
Best Answer chosen by Jaclyn Figueroa
Steve MolisSteve Molis
So something kinda like this?
Due_Date__c - 
CASE( 
MOD( Due_Date__c - DATE( 1900, 1, 7 ), 7 ) + 1,
1, (3 + 7),
2, (4 + 7),
3, (4 + 7),
4, (2 + 7),
5, (2 + 7),
6, (2 + 7),
7, (2 + 7),
0)

 

All Answers

Steve MolisSteve Molis
So something kinda like this?
Due_Date__c - 
CASE( 
MOD( Due_Date__c - DATE( 1900, 1, 7 ), 7 ) + 1,
1, (3 + 7),
2, (4 + 7),
3, (4 + 7),
4, (2 + 7),
5, (2 + 7),
6, (2 + 7),
7, (2 + 7),
0)

 
This was selected as the best answer
Jaclyn FigueroaJaclyn Figueroa
Would I change the "7" to "10" like this if I needed to change it to 10 business days?

Due_Date__c - 
CASE( 
MOD( Due_Date__c - DATE( 1900, 1, 7 ), 7 ) + 1,
1, (3 + 10),
2, (4 + 10),
3, (4 + 10),
4, (2 + 10),
5, (2 + 10),
6, (2 + 10),
7, (2 + 10),
0)
Steve MolisSteve Molis
No, the 7 is the Business Days, the number on the left of the + sign is the weekend days being skipped.  Instead of saying (3 + 7), (4 + 7),(2 + 7), you could just say 10, 11, 9.  I just like writing out my math because I'm an idiot and I need to see it in front of me.
Aamir KhanAamir Khan
Aloha Jaclyn,

Steave you did a greate job but we can try it with more simpler way with WEEKDAY function of formula field.
IF( 
	/*WEEKDAY function return the number of day if 7 i.e. Saturday or if 1 i.e. Sunday*/
	(WEEKDAY( Due_Date__c - 7)  == 7), /* Here first we are making Due_Date__c 7 days before by subtraction.*/ 
		(Due_Date__c - 1),
		IF(
			(WEEKDAY( Due_Date__c - 7)  == 1),
			(Due_Date__c - 2),
			Due_Date__c
		)
)

So if your Reminder_Due_Date__c is Saturday this formula return date of 1 day before i.e. Friday and if Reminder_Due_Date__c is Sunday this formula will return date of 2 days before i.e. Friday.

Hope it helps.

Please mark solved if it works.

Mahalo,
Aamir AH Khan,
Lead Salesforce Developer
Jaclyn FigueroaJaclyn Figueroa
Can someone help me re-write this for 10 business days? Sorry I am confused. I re-wrote like this:
Due_Date__c - 
CASE( 
MOD( Due_Date__c - DATE( 1900, 1, 7 ), 7 ) + 1,
1, (3 + 10),
2, (4 + 10),
3, (4 + 10),
4, (2 + 10),
5, (2 + 10),
6, (2 + 10),
7, (2 + 10),
0)

But that is wrong? It seemed to work. 
 
Steve MolisSteve Molis
The difference 10 Business Dyas covers 2 whole weekends if the Due Date is Monday-Friday, try something like this
Due_Date__c - 
CASE( 
MOD( Due_Date__c - DATE( 1900, 1, 7 ), 7 ) + 1,
1, (3 + 10),
2, (4 + 10),
3, (4 + 10),
4, (4 + 10),
5, (4 + 10),
6, (4 + 10),
7, (2 + 10),
0)