Apex Code Development You need to sign in to do that
Don't have an account?
Jordan Donolow Specific Workdays Formula Question
Hey all,
Hope all is well! I wanted to ask for a huge favor here with the workday formula.
I am looking for a formula that will calculate 6 days in the past from the start date.
Out of the 6 total count days:
Day 0: cannot count Saturday, Sunday or Holiday's
Day 1-3: cannot count Sunday or Holiday's
Day 4-6: cannot count Saturday, Sunday or Holiday's
However, the final result can only land on a Monday-Friday and not on a holiday, saturday or sunday
So if it did land on a Holiday, Saturday or Sunday, the end date would revert to the previous workday
Example:
If start date is 1/18/2022, should return 1/7/2022, NOT 1/10/2022
Day 0: 1/7 (return date) since Day 0 cannot be a Sunday (see above), so the return date should revert to the previous workday (1/7/2022)
Day 1: 1/10
Day 2: 1/11
Day 3: 1/12
Day 4: 1/13
Day 5: 1/14
Day 6: 1/18 (start date)
1/15 doesnt count since its a Saturday
1/16 doesnt count since its a Sunday
1/17 doesnt count since its a Holiday (MLK Jr)
Here is the WORKING formula in excel:
=if(B7="CA", WORKDAY(WORKDAY.INTL(WORKDAY(AF7,-3,$A$2:$A$13),-3,11,$A$2:$A$13)+1,-1,$A$2:$A$13), AF7-4)
Your help would be greatly appreciated!!
Hope all is well! I wanted to ask for a huge favor here with the workday formula.
I am looking for a formula that will calculate 6 days in the past from the start date.
Out of the 6 total count days:
Day 0: cannot count Saturday, Sunday or Holiday's
Day 1-3: cannot count Sunday or Holiday's
Day 4-6: cannot count Saturday, Sunday or Holiday's
However, the final result can only land on a Monday-Friday and not on a holiday, saturday or sunday
So if it did land on a Holiday, Saturday or Sunday, the end date would revert to the previous workday
Example:
If start date is 1/18/2022, should return 1/7/2022, NOT 1/10/2022
Day 0: 1/7 (return date) since Day 0 cannot be a Sunday (see above), so the return date should revert to the previous workday (1/7/2022)
Day 1: 1/10
Day 2: 1/11
Day 3: 1/12
Day 4: 1/13
Day 5: 1/14
Day 6: 1/18 (start date)
1/15 doesnt count since its a Saturday
1/16 doesnt count since its a Sunday
1/17 doesnt count since its a Holiday (MLK Jr)
Here is the WORKING formula in excel:
=if(B7="CA", WORKDAY(WORKDAY.INTL(WORKDAY(AF7,-3,$A$2:$A$13),-3,11,$A$2:$A$13)+1,-1,$A$2:$A$13), AF7-4)
In the Loan Object in Salesforce:
B7 = ‘Property State’ loan field
AF7 = ‘Close of Escrow’ loan field
$A$2:$A$13 = Current Holiday Date List below:
1/17/2022 MLK
2/21/2022 Presidents
5/30/2022 Memorial
6/20/2022 Juneteenth
7/4/2022 Independence
9/5/2022 Labor Day
10/10/2022 Columbus
11/11/2022 Veterans
11/24/2022 Thanksgiving
11/25/2022 Day After Thanksgiving
12/26/2022 Christmas
1/2/2023 New Years
Would want to translate this formula into a Salesforce formula2/21/2022 Presidents
5/30/2022 Memorial
6/20/2022 Juneteenth
7/4/2022 Independence
9/5/2022 Labor Day
10/10/2022 Columbus
11/11/2022 Veterans
11/24/2022 Thanksgiving
11/25/2022 Day After Thanksgiving
12/26/2022 Christmas
1/2/2023 New Years
Your help would be greatly appreciated!!
First idea:
formula1: HOLLYDAYS DAY-5: IF(CONTAINS('2022-01-17,2022-02-21,2022-05-30,2022-06-20,2022-07-04,2022-09-05,2022-10-10,2022-11-11,2022-11-24,2022-11-25,2023-01-02',TEXT(start_date__c - 1)),start_date__c - 2,start_date__c - 1)
+
formula2: WEEKDAY ( HOLLYDAYS DAY-5 ) = 1 (Sunday) => date - 2 or 7 (Saturday) => date - 1
+
formula3: HOLLYDAYS DAY-4 by using formula2 and so on.
could be used and combined for the day 5 to 0 by using the formulas of the previous day for the current day with as many formulas as needed but the size for the formula calculating the next day will overcome the maxium compiled size from the day 4 (you cannot calculate all the days by this method).
11/24/2022 Thanksgiving
11/25/2022 Day After Thanksgiving => closed dates and that complicates the formulas (recalculation).
Error: Compiled formula is too big to execute (28 881 characters). Maximum size is 15 000 characters
The workaround is to use Apex and a trigger (or a Flow) to store the final calculated date (day 0) during the events of creation/update for this start-date (preventing the loop when updating the same record that triggered the event).