You need to sign in to do that
Don't have an account?
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?
Thanks in advance.
All Answers
This worked stellar! Thanks so much!
Shruthi,
I want to find the number of days.
Excluding Saturdays and Sundays.
How can I do this?
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)
Thanx Shruthi, that works fine.
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!!!
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
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
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.
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
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.
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
Thank you in advance.
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)
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,"
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:
- Assigned Date (Assigned_Date__c)
- Due Date (Due_Date__c)
Our Objective is that Due Date = Assigned Date + 72 hours (Excluding Weekends)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
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
Fb Status hindi >>>CLICK HERE 》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Whatsaap Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Attitude-status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Gf-Bf Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Royal Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Gf-Bf Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Attitude status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Royal Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
best Facebook Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
A-Z Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Dardbari status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Birthday Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Funny Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Sad Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Fb Status hindi >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Whatsaap Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Attitude-status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Gf-Bf Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Royal Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Gf-Bf Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Attitude status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Royal Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
best Facebook Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
A-Z Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Dardbari status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Birthday Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Funny Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Sad Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Fb Status hindi >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Whatsaap Status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Attitude-status >>>CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Gf-Bf Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Royal Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Gf-Bf Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Attitude status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Royal Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
best Facebook Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
A-Z Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Dardbari status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Birthday Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
Funny Status >>> CLICK HERE》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.htmlSad Status >>> CLICK HER》》https://www.fbstatusinhindi.xyz/2020/12/fb-status-hindi.html
https://techost.livejournal.com http://doremi.over-blog.com
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.