Don't have an account?

Browse by Topic

+ Start a Discussion

Need a formula to calculate a date field counting backwards from another date field that excludes weekends.

IF(MOD([MPM4_BASE__Milestone1_Project__c].Go_Live_Date__c - DATE(1900,1,14),7)<5, [MPM4_BASE__Milestone1_Project__c].Go_Live_Date__c - 80,

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

Then I tried
CASE(MOD( MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - DATE(1900,1,7),7),
0,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c-80),
1,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c-80),
2,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c-80),
3,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c-(80+2)),
4,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c-(80+2)),
5,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c-(80+2)),
6,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c-(80+1)),
MPM4_BASE__Project_Lookup__r.Go_Live_Date__c -80)

Please let me know what I am doing wrong? I entered 1/1/2019 as the Go Live Date and I still get 10/13/2018 ?

Thank you,
Dorel

August 21, 2018
·
Like
0
·
Follow
1

Best Answer chosen by Dorel Nasso 9

Alain Cabon
Hi,

You want to get: DATE2 = DATA1 - 80 business days (with 5 days par week)

80 business days (with 5 days per week) = 16 weeks x 7 = 112 days (with 7 days per week)

1) Adding Business Days to a Date:

This formula finds three business days from a given date ( but here the number of days is below five and seven, less than a week )
CASE( MOD( date - DATE( 1900, 1, 7 ), 7 ), 3, date + 2 + 3, 4, date + 2 + 3, 5, date + 2 + 3, 6, date + 1 + 3, date + 3 )

This formula finds the day of the week of the date field value. If the date is a Wednesday, Thursday, or Friday, the formula adds five calendar days (two weekend days, three weekdays) to the date to account for the weekend. If date is a Saturday, you need four additional calendar days. For any other day of the week (Sunday — Tuesday), simply add three days. You can easily modify this formula to add more or less business days. The tip for getting the day of the week might be useful if you need to adjust this formula.

https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5

2) Subtracting a number of days from a date to find a past date without the week-ends:

http://people.albion.edu/imacinnes/calendar/Day_of_the_Week.html

CASE(MOD( MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - DATE(1900,1,7),7), 0,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112 - 2 ), // Sunday (-2 days = previous Friday) 1,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112), // Monday 2,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112), // Thuesday 3,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112), // Wednesday 4,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112), // Thursday 5,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112), // Friday 6,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112 - 1 ), // Saturday (-1 day = previous Friday) MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 112) // never used

MOD( DATE1 - DATE(1900,1,7),7) = 0 so DATE1 is the same day of 1/7/1900 = Sunday

August 21, 2018
·
Like
0
·
Dislike
0

This was selected as the best answer

Dorel Nasso 9
Alain, thank you for your help and explaining everything to me but it still is not working. I still get weekends. If my Go Live Date is January 1st, I am still getting October 13th (Saturday)
CASE(MOD( MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - DATE(1900,1,7),7),
0,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80 - 2 ),
1,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80),
2,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80),
3,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80),
4,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80),
5,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80),
6,(MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80 - 1 ),
MPM4_BASE__Project_Lookup__r.Go_Live_Date__c - 80)

August 22, 2018
·
Like
0
·
Dislike
0

Alain Cabon
Hi Doral,

In fact, you must use 112 days (including the week-ends) and not 80 opened days..

You just want 16 weeks before the reference date without the week-ends in the result.

The formula with 112 days works with complete weeks but it is a simplification because we can convert your 80 opened days in 16 complete weeks of 5 days each (or 16 complete weeks of 7 days and we just change the results for the days in week-ends (Saturday and Sunday)).

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
Alain, So I need to add 32 to any number of days I want to subtract from the Go Live Date if I am working on a 90 day timeline? I have timelines from 1 day before go live to 95 days before go live. So, 112 for 80 days before and 62 for 30 days? Or do I 112 - 80 to get 80 days before?

August 22, 2018
·
Like
0
·
Dislike
0

Alain Cabon
No, you must convert the number of opened days in complete weeks of seven weeks + the remaining days.

For example, if you want to subtract 8 opened days, it is a 1 weeks of 5 days + 3 days = 1 x 7 + 3 = 10 days

17 opened days, it is 3 weeks of 5 days + 2 days = 3 x 7 + 2 = 23 days

That is why the formula with 112 days is a simplification.

With 82 opened days, the formula would have been totally different.

There are many tricks like that used in these formulas with dates because that could be very complicated when you want a generic formula.
Salesforce doesn't provide this generic formula. You tried their formula and that doesn't work because it is not a generic formula.

The problem is that we cannot always write the generic formula because it would be too huge and there is a limit for the size of the compiled formula.

That could be possible to write the generic formula here but did you need to change the number of opened days very often?

It is more easy to change it manually after a small conversion towards a week of 7 days instead of a week of 5 days.

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
I have over 30 different variations of the formula, ranging from 1 to 95 days. thank you for your help.

August 22, 2018
·
Like
0
·
Dislike
0

Alain Cabon
Ok so it is interesting to convert the numbers of opened days in the formula directly.

In fact, Salesforce has provided a precise algortihm but it is in Apex only.

The basic formula will be something like the formula below (not tested)
by replacing TODAY() with MPM4_BASE__Project_Lookup__r.Go_Live_Date__c
and Service_Lead_Days__c with 80 opened days directly
TODAY() /* today's date */ + Service_Lead_Days__c /* + the lead time */ + (2 * FLOOR(Service_Lead_Days__c / 5)) /* + the number of weekend days */ + CASE(MOD( (Today() + MOD(Service_Lead_Days__c, 5)) - DATE (2000 ,1, 1) ,7), /* calculate the day of week for the release date based on 1/1/2000 being Saturday */ 0,2, /* + 2 days if the calculated release day would be a Saturday */ 1,1, /* + 1 day if the calculated release day would be a Sunday */ 0) /* + 0 days if the calculated release day would be a weekday */ + IF(Service_Lead_Days__c < 5, /* + 2 days for short projects that would span a weekend */ IF(MOD(Today() - DATE (2000 ,1, 1) ,7) + Service_Lead_Days__c > 6, 2, 0), 0)
He used 1/1/2000 instead of 1/7/1900 but you retrieve all the known tricks;

But it is an addition here and not a subtraction of opened days.

https://developer.salesforce.com/forums/?id=906F00000008wNHIAY

Replacing some "+" by "-" could be sufficient.

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
Thank you. have a great day

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
When I do the 112 day, I get September 11, which is 112 days from January 1st including weekends. I will try the other formula and let you know what happens. Thank you for your help

August 22, 2018
·
Like
0
·
Dislike
0

Alain Cabon
Ok but it is how many opened days from January 1st?

You want to substract 80 days including the week-ends or 80 opened days without the week-ends?

In fact, you will be always disapointed including using the formulas given by Salesforce (simplifications most of the time).

For the following questions, we have used a formula given by Salesforce for another problem: https://developer.salesforce.com/forums/?id=9060G000000XhyAQAS And we even tried formulas from other forums but there is always a case that doesn't work.
These formulas are often incomplete. They work most of the time but not for all the cases or they need to be much more complicated and you can exceed the limit for the size of a formula.

I am interested by these questions and that could be great to have the most precise formula at the end of your question including from people on other forums ( you can try other forums with the same question ).

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
I need 80 days from the Go Live Date, if it lands on a weekend I need it to moved to either Friday or Monday. 80 day before January 1st is October 13th. October 13, 2018 is a Saturday, I need it to be either the 12th (Friday) or 15th (Monday). I want to count weekends but not have the ETA Date on a weekend. So I want to add days to move it to the next work day.

August 22, 2018
·
Like
0
·
Dislike
0

Alain Cabon
The best choice is 111 days.

Number of weeks x 7 - 1 day = 16 x 7 - 1 = 111

CASE(MOD( date1__c - DATE(1900,1,7),7), 0,( date1__c - 111 - 2 ), 1,( date1__c - 111), 2,( date1__c - 111), 3,( date1__c - 111), 4,( date1__c - 111), 5,( date1__c - 111), 6,( date1__c - 111 - 1 ), date1__c - 111)

You can verify the number of days between two date without the week-ends with the folowing formula;
CASE(MOD( date2__c - DATE(1985,6,24),7), 0 , CASE( MOD( date1__c - date2__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( date1__c - date2__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( date1__c - date2__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( date1__c - date2__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( date1__c - date2__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( date1__c - date2__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( date1__c - date2__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( date1__c - date2__c )/7)*5)

August 22, 2018
·
Like
0
·
Dislike
0

Alain Cabon
+/- one day is a bit subjective when you remove or add days.

Monday + 1 day = Tuesday (morning) or end of Monday ?
Monday + 2 days = Wednesday
Monday + 3 day = Thursday
Monday + 4 days = Friday (so a week is only 4 days ?)

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
Thank you for your help Alain but I do not think I am explaining myself correctly. I want to count weeknds but I do not want my due date to be on a weekend. So I need to add 1 or 2 days to the saturday or sunday formula days. I want 80 days from the Go to live date. If that date is on a saturday, I need to add a 1 or 2 days to move it to a weekday. I want either the monday or the friday 78 or 81 days.

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
This is want I am trying to get
Monday: Go Live Date - 80 days
Tuesday:Go Live Date - 80 days
Wednesday: Go Live Date - 80 days
Thursday: Go Live Date - 80 days
Friday: Go Live Date - 80 days
Saturday: Go Live Date - 81 days
Sunday: Go Live Date - 82 days

August 22, 2018
·
Like
0
·
Dislike
0

Dorel Nasso 9
I think realized why you thought I did not want to count weekends from my header. I want to count weekends in the 80 but if that 80th day is on a weekend I want to move the due date to a weekday

August 22, 2018
·
Like
0
·
Dislike
0

Alain Cabon
Ok it is not the same formula indeed and it is simpler and you were close to the solution at the beginning.

CASE(MOD( (date1__c - 80) - DATE(1900,1,7),7), 0,( date1__c - 80 - 2 ), 1,( date1__c - 80), 2,( date1__c - 80), 3,( date1__c - 80), 4,( date1__c - 80), 5,( date1__c - 80), 6,( date1__c - 80 - 1 ), date1__c - 80)

August 22, 2018
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Need a formula to calculate a date field counting backwards from another date field that excludes weekends.

All Answers

You need to sign in to do that.