ShowAll Questionssorted byDate Posted
RRRize

# 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?

shruthi

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!

shruthi

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
RRRize

This worked stellar!  Thanks so much!

cvuyyuru

Shruthi,

I want to find the number of days.

Excluding Saturdays and Sundays.

How can I do this?

shruthi

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

cvuyyuru

Thanx Shruthi, that works fine.

Amit 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??

shruthi

Hi Amit

check out the above website for a solution on due date

akschamp

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

Riz234

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.

Newbie2013

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

RizwanKSA

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_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 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 ?

Mandi (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 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
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) 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:
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
)

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 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 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 Seara
what about 60 days from today ?
Amit 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.

Regards,

Amit

Mahi Singh 6
https://www.toplyricsonline.com
Mahi Singh 6
Thanks (https://mahiworlds.com) a lot. You have done an excellent job. I enjoyed your blog (https://www.toplyricsonline.com). Nice efforts
Priyam Katiyar
If you are a shayari or poetry lover then you can visit love shayari. (https://weloveshayari.com/love-shayari/)
Arsalan Pervez
Thank You for sharing such a nice article. Clipping Path Service (https://it-s.com/our-services/image-editing/clipping-path/)
Fb Status in hindi
I recently found this site as it is so helpful. there are many times before searching a solution to my problem...For more....

Robert howard 5
Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

https://techost.livejournal.com http://doremi.over-blog.com
MyArticles is a writer’s community where writers can share their stories all over the world. Signup and share your stories to all over the world. Follow your favorite writers, create groups, forums, chat, and much much more!

Edgar Liu
@shruthi - Thank you for posting the formula for the number of days. For some reason, I have to modify your formula a little bit because it was miscalculating when the created date was on Wednesday or Thursday. Here is the formula I modified;

CASE(
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 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)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)﻿

Note: The day of the week starts on Sunday:
0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday"
I hope this helps.