function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Freddy99Freddy99 

Subtract x Business Days from Today() in Formula

Hy Guys,

 

I've a workflow rule with a field update as action.

My new field value shall be: Today() - 3 Business days.

How can I calculate that only Business days are subtracted?

Can anybody help me?

 

Thanks and best regards,

Freddy 

Message Edited by Freddy99 on 02-23-2010 12:21 AM
Best Answer chosen by Admin (Salesforce Developers) 
Freddy99Freddy99

Thanks indy_sfdc,

 

I implemented like your solution, see following code: 

 

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

 

Regards,

Freddy 

All Answers

indy_sfdcindy_sfdc

Hi Freddy

 

I believe this formula will get you to Weekdays, I did not test extensively. 

 

IF ( MOD((TODAY()-3) - DATE(1900, 1, 7), 7) = 0,
TODAY()-5,
 (IF ( MOD((TODAY()-3) - DATE(1900, 1, 7), 7) = 6, TODAY()-4, TODAY()-3) )
)
 

Obviously there is a difference between weekdays and business days, because calculating actual business days can vary by region, company and country so you may have to go the APEX trigger route to take in all your region, company or country specifics and still keep some level of configurability to you application.

 

 

 
Freddy99Freddy99

Thanks indy_sfdc,

 

I implemented like your solution, see following code: 

 

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

 

Regards,

Freddy 

This was selected as the best answer
werewolfwerewolf
You also could have used the BusinessHours class in Apex to do these calculations.
indy_sfdcindy_sfdc

Agree with the APEX option.  For more information on using the BusinessHours class take a look at this link:

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_classes_businesshours.htm?SearchType=Stem

 

 

 

 

 

 
AMR27AMR27

indy,

 

is there some sort of tutorial available on  utilizing the business hours class to help with the Business Day issue?  I am vaguely familiar with APEX and triggers, and I feel like this is something I can handle if I saw some code/was started in the right direction, however I have literally no idea where to start.  Please advise.

 

Thanks!
Jonathan.AdamsJonathan.Adams

I'm attempting to modify this formula slightly and I'm having a few issues. My situation needs to take an existing date, not today and subtract a number field as opposed to a hard coded value. Here's my code: 

 

Install_Date__c   - 

IF(MOD(Install_Date__c  - DATE(1900, 1, 7), 7) <= Delivery_Duration__c, (Delivery_Duration__c+2), 

IF(MOD(Install_Date__c - DATE(1900, 1, 7), 7) = 6, (Delivery_Duration__c +1),Delivery_Duration__c))

 

The formula seems to work just fine if the Delivery Duration value is 5 or less. It will skip over the weekends as expected. As soon as I change the delivery duration to 6 or higher, the formula breaks. Can someone help me troubleshoot this? 

 

I would GREATLY appreciate your help! 
Jonathan.AdamsJonathan.Adams

In a perfect world, the formula would be able to exclude holidays as well. Similar to the WORKDAY function in Excel. 
luckme10luckme10

I was wondering if someone could wak be through the perimeters of this equation. I'm prettty new to setting up formulas on here and I need to set up a formula to make the following functions

now()+3 Business days

now()+5 Business days

 

 Thanks

 

Using the above formula what do the numbers represent in the right side?

TODAY() -+
IF(MOD(TODAY() - DATE(1900, 1, 7), 7) <= ?, (?), 
IF(MOD(TODAY() - DATE(1900, 1, 7), 7) = ?, (?), ?) 
)
WEI Admin 9WEI Admin 9
Here's my solution for the scenario where I want to populate a date field with a date that is 3 weeks (21 days) before another date field (Start_Date__c), but the new date should not land on a Saturday or Sunday:

IF(MOD( ([EventApi__Event__c].Start_Date__c - 21) - DATE(1900, 1, 7), 7)=0, [EventApi__Event__c].Start_Date__c - 23, 
IF(MOD( ([EventApi__Event__c].Start_Date__c - 21) - DATE(1900, 1, 7), 7)=6, [EventApi__Event__c].Start_Date__c - 22, 
[EventApi__Event__c].Start_Date__c - 21) )