+ Start a Discussion
RRRizeRRRize 

Date Field - Exclude Weekends in formula?

I currently have a date field that displays a date 3 days from the date the form is opened. Here is the formula I used: TODAY() + 3

 

I would like to modify the formula to show a value of 3 business days instead of 3 days.  Can someone help?


Thanks in advance.

Best Answer chosen by Admin (Salesforce Developers) 
shruthishruthi

 

CASE(MOD(TODAY()- DATE(1900, 1, 7), 7), 0, TODAY()+3, 1, TODAY()+3, 2, TODAY()+3,3, TODAY()+5, 4, TODAY()+5, 5, TODAY()+5, 6, TODAY()+4,null)
The above formula can be used to show TODAY()+3 business days [excluding saturday and sunday]. Please let me know if this helps.
Cheers!

 

All Answers

shruthishruthi

 

CASE(MOD(TODAY()- DATE(1900, 1, 7), 7), 0, TODAY()+3, 1, TODAY()+3, 2, TODAY()+3,3, TODAY()+5, 4, TODAY()+5, 5, TODAY()+5, 6, TODAY()+4,null)
The above formula can be used to show TODAY()+3 business days [excluding saturday and sunday]. Please let me know if this helps.
Cheers!

 

This was selected as the best answer
RRRizeRRRize

This worked stellar!  Thanks so much!

cvuyyurucvuyyuru

Shruthi,

 

 

I want to find the number of days.

 

 

Excluding Saturdays and Sundays.

 

 

How can I do this?

shruthishruthi

If you want to find the number of days from the CreatedDate, you can do it using the below formula. It excludes Saturdays and Sundays when calculating the age/ number of days from the created date till today.

 

CASE(
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)

cvuyyurucvuyyuru

Thanx Shruthi, that works fine.

Amit Singh.ax1042Amit Singh.ax1042

shruti

i have fields start date (data type is Date)... and no. of days (data type is no.)

 

now i want to create due date field where,

due date = start date + no. of days excluding sat / sun


how it is possible??

 

Thanks in advance!!!

shruthishruthi

Hi Amit

 

http://salesforce-shruthi.blogspot.com/2011/09/exclude-saturdays-and-sundays-while.html

 

check out the above website for a solution on due date

akschampakschamp

Hi Shruthi,

 

I want to Show Monday Date If Today's Date is Saturday Or Sunday, Pelase let me know how can I do this?

 

Regards,

Akshay

Riz234Riz234

I have one custom object "Bid" and custom field "Bid Submission date", I want to create a validation,  User should be able to submit the request only if "Submission date" is more than 3 business days from Today.

 

Eg: Today is 17th Mar

       Submission date is 19th Mar (validation should occur)

       Submisison date is  21st Mar (no validation)

       submission date is 21st mar and if there is weekend (thursday & Friday), validation should occur.

 

we work in Saudi arabia and weekend is Thursday and Friday.

 

Need help and Support.

Newbie2013Newbie2013

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

 

This workds perfect for me. But there is one issue - I am replacing a date field in place of TODAY() , and I want it to add 3 days everytime, but when The date field is set to friday, it should only show tuesday not thursday. Can you help me with that? 

Thanks

RizwanKSARizwanKSA

Dear Shruthi,

 

How this will work if we are in Middle east where weekend is Friday & Saturday. I didn't understand this formula.

 

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

 

I NEED THIS FORMULA WHERE FRIDAY & SATURDAY ARE WEEKEND.

NIKHIL_SFDCNIKHIL_SFDC
What if I just want to add dynamic number of days (from a numaric field)
I just want to populate EndDate__c= StartDate__c + NumberOfBusinessDays__c. (whereas StartDate__c is a date field and NumberOfBusinessDays__c is a number field (4,0))
EndDate__c should not fall in weekend date.

Thanks,
Nikhil
Anupama BalaharaAnupama Balahara
RizwanKSA- We have a similar requirement where we need to calculate weekends based on Middle East regions. Were you able to figure it out how to achieve this ?

Thank you in advance.
Mandi (Loomis) BlackfordMandi (Loomis) Blackford
Hi! I am trying to do the reverse of the original question - 3 days prior to a date. I copied the formula provided above and changed the + to -, but it's not working. 

I need 3 days prior to arrival to display in my formula field.

CASE(MOD(nihrm__ArrivalDate__c - DATE(1900, 1, 7), 7), 0, nihrm__ArrivalDate__c -3, 1, nihrm__ArrivalDate__c -3, 2, nihrm__ArrivalDate__c -3,3, nihrm__ArrivalDate__c -5, 4, nihrm__ArrivalDate__c -5, 5, nihrm__ArrivalDate__c -5, 6, nihrm__ArrivalDate__c -4,null)
Mohan Raj 33Mohan Raj 33
@shruthi, Can you modify this below formula to If I select the Saturday/Sunday also It's should be omitted my selection and calculated the date difference the next working day (for here I mean that my selected saturday/sunday value's upcoming monday) ?,Thank you, Mohan
Maddie Goldman 9Maddie Goldman 9
I'm trying to modify this formula to get a due date that is 60 business days from the created date. Can anyone help with this? Thanks!
Mandi (Loomis) BlackfordMandi (Loomis) Blackford
Here's what I figured out using this post as a starting point - I needed a formula field for 5 business days prior to arrival, and another for 3 business days prior to arrival. This is what I did:
Five Business Days:
CASE( 
MOD( nihrm__ArrivalDate__c - DATE( 1900, 1, 7 ), 7 ), 
1, nihrm__ArrivalDate__c - 2 - 5, 
2, nihrm__ArrivalDate__c - 2 - 5, 
3, nihrm__ArrivalDate__c - 2 - 5, 
4, nihrm__ArrivalDate__c - 2 - 5, 
5, nihrm__ArrivalDate__c - 2 - 5, 
6, nihrm__ArrivalDate__c - 5, 
nihrm__ArrivalDate__c - 1 - 5 
)
 
Three Business Days:
CASE( 
MOD( nihrm__ArrivalDate__c - DATE( 1900, 1, 7 ), 7 ), 
1, nihrm__ArrivalDate__c - 2 - 3, 
2, nihrm__ArrivalDate__c - 2 - 3, 
3, nihrm__ArrivalDate__c - 2 - 3, 
4, nihrm__ArrivalDate__c - 3, 
5, nihrm__ArrivalDate__c - 3, 
6, nihrm__ArrivalDate__c - 3, 
nihrm__ArrivalDate__c - 2 - 2 
)
 
In case you need to calculate other ranges, 0 = Sunday, 1 = Monday, etc. Count backwards from the day of the week to see how many days you need to get to a business day. In the formula above, 2 means it needs to count for the weekend, otherwise it's just - the number of days. For example, 3=Wednesday. If I want to get 3 business days prior to Wednesday, 3 days before Wednesday is Sunday, which is a weekend, so I want to subtract 3 business days and 2 weekend days, therefore in the formula it has "3, nihrm__ArrivalDate__c - 2 - 3,"
Mike Johnson 1Mike Johnson 1
CASE(MOD(TODAY()- DATE(1900, 1, 7), 7), 0, TODAY()+3, 1, TODAY()+3, 2, TODAY()+3,3, TODAY()+5, 4, TODAY()+5, 5, TODAY()+5, 6, TODAY()+4,null)

My Question: How would I modify the formula field above to address a Date/Time field instead

In our scenario we have two Date/Time fields: 
  1. Assigned Date (Assigned_Date__c)
  2. Due Date (Due_Date__c)
Our Objective is that Due Date = Assigned Date + 72 hours (Excluding Weekends)
  • Workflow Rule Criteria: Assigned Date != Null
  • Workflow Action: Update Due Date
  • Evaluation Criteria: Anytime Edited to meet criteria
Cody HlavinkaCody Hlavinka
Inreference to the formaula below, i want to add 5 days to it, what would it look like? I am trying to figure out which numbers to switch out, please help, thanks.

CASE(MOD(TODAY()- DATE(1900, 1, 7), 7), 0, TODAY()+3, 1, TODAY()+3, 2, TODAY()+3,3, TODAY()+5, 4, TODAY()+5, 5, TODAY()+5, 6, TODAY()+4,null)
The above formula can be used to show TODAY()+3 business days [excluding saturday and sunday]. @shruthi
Victor SearaVictor Seara
what about 60 days from today ?
Amit Patil 1Amit Patil 1

Hi Shruti,

 

CASE(
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)

The above formula is working, but my requirement is, I need the difference in Hours. I tried to edit the formula but not able to find out the proper solution.

 

Thanks in Advance!

 

Regards,

Amit