function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DataRecoveryNederlandDataRecoveryNederland 

Formula without weekends.

Hey, 

 

i got a formula that creates an enddate from a startdate without weekens

 

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

 

this worked great for 4 workdays. 

 

now i want to make the 4 workdays a variable so it can be 4,5 or 6 or even 20 (dropdown menu)

and im lost cause i dont know how to calculate the exact amount of weekend days without exceeding the 5000 limit.

i made the 2 following formulas;

 

111 duur expres eind:

 

CASE(Recovery_duur_Expres__c,

"4", CASE(MOD(DATEVALUE(Data_recovery_akkoord__c ) - DATE(1900, 1, 7) , 7),
0, 4,
1, 4,
2, 6,
3, 6,
4, 6,
5, 6,
6, 5,null),
"5", CASE(MOD(DATEVALUE(Data_recovery_akkoord__c ) - DATE(1900, 1, 7) , 7),
0, 5,
1, 7,
2, 7,
3, 7,
4, 7,
5, 7,
6, 6,null),
null)

 

 

 

111dagen test:

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

 

 

 

 

this gets me way over the 5000 limit.

can anyone help me.

Jeff MayJeff May

Using your current approach of 1900-01-07 as a Sunday (day of week = 0), and adding a numeric custom field to your record to hold the "workdays__c", the following will adjust for non-weekends.  (I used the object CreatedDate for this example)

 

 

CreatedDate  + 
workdays__c + 
(IF(workdays__c > 5, ((floor(workdays__c / 5)-1) * 2), 0)) 
+
(if (workdays__c > (5 - (MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7) , 7))), 
       IF(mod(workdays__c , 5) > 0, 7-(MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7) , 7)),0),0) 
)

 

I tested this on a few dates, it seems to work, and it compiles to 711 characters.  That leaves room in case you want to have "workdays__c" be a formula instead of a number.

 
DataRecoveryNederlandDataRecoveryNederland

it seems that your code doesnt include days, just weeks.

 

ill explain, whatever day i pick of this week the formula picks todays day (monday) in a week.

 

so i i pick 6 workdays it would be correct but if i pick 7,8 or 9 workdays it would al say the same day ( monday) but when i pick 11,12,13,14 it would pick monday the week after.

 

so again your formula picks the same day of the week no mather how many workdays i select within that week.

 

my english sucks hope i explained it correctly 
Jeff MayJeff May
Your English is pretty good. I will test my formula again and also my cut/paste into the post, The intent of the formula is to add a number of "calendar days" to a start date so that weekends are not included. To do this, it figures out how many workdays are in the first week -- may be a partial week. Then some number of full weeks in the middle. Then how many days are in the final partial week. Then adds in the number of "weekend days".
DataRecoveryNederlandDataRecoveryNederland

thank you for your quick reply,

 

my goal is simple.

 

i got a startdate__c + workdays__c = enddate__c (without weekends ofcourse)

 

i have to be able to pick the starting day myself.

and the number of workdays is a variable\

 
T-HanT-Han

Any help on this pls? I am tryin to use the formula above but havn a hard time here. Newbie to SF. !!

 

Start Date 1 - Date field, End Date 1 - Formula Field.

Start Date 2 - Date field, End Date 2 - Formula Field.

 

End Date 2 = {Start Date 1 + (3 days) } - but it should not be a Weekend.

 

If its a weekend, then it has to be a next weekday.

Eg: If "End Date 2 calculates to be - Saturday", the formula should calculate and the answer should be Monday's Date.

 

On the whole End Dates should have the dates from M-F.

 

Assistance on this is highly appreciated.? 
DataRecoveryNederlandDataRecoveryNederland

here is how this formula works. if you have a static +3 days all over then here is what you get.

 

 

end date formula.

 

CASE(MOD(DATEVALUE(Start Date) - DATE(1900, 1, 7) , 7),
0, Start Date+4,
1, Start Date+3,
2, Start Date+3,
3, Start Date+5,
4, Start Date+5,
5, Start Date+5,
6, Start Date+4,

null)

 

 

ill explain why so you can try and make your own.

 

CASE(MOD(DATEVALUE(Start Date) - DATE(1900, 1, 7) , 7),       /// here it checks what day to start checking on a sunday long in the past ( 1900-1-7.)  to check what day in the week start day is


0, Start Date+4,                                                                                      /// the above formula makes 0 a sunday so do sunday + 4 makes it thursday
1, Start Date+3,                                                                                      /// the above formula makes 0 a monday so do monday+ 4 makes it thursday                                                                            
2, Start Date+3,                                                                                      ///etc
3, Start Date+5,                                                                                
4, Start Date+5,                                                                                  
5, Start Date+5,
6, Start Date+4,

null)

 

if you want to shorten your formula for limit cases. take the + you use the most and remove them. putting them in the NULL area.

 

CASE(MOD(DATEVALUE(Start Date) - DATE(1900, 1, 7) , 7),
0, Start Date+4,
1, Start Date+3,
2, Start Date+3,
6, Start Date+4,

Start Date+5)

 

hope this helps

 

 
T-HanT-Han

HI,

Thanks - @ DataRecoveryNederland

 

I tried the exact same formula as you've given with my Custom Date fields in it. But, i am getting an error 

 

" Error: Incorrect argument type for function 'DATEVALUE()'."

 

My Return Type is a DATE field.

 

My scenario is to calculate 4 Auto-Calculated Date fields.

 

First Date : Start Date + 1 day increase

Second Date: Start Date + 2 day increase

Third Date: Start Date + 3 day increase

Fourth Date: Start Date + 4 day increase.

 

The results of all the above Dates should be a Weekday- Date.

 

Still need assistance pls.. ! Sry fr having to Hang here more.! Need this one fr me.! 
T-HanT-Han

Hi Again,

 

I change the Field Type to Date/Time and was able to execute the formula you provided.. 

 

Still having Hick Ups with the Date increments of 1, 2,3,4 days ..

 

Assistance..?

 

Thanks
T-HanT-Han

Hi,

 

Checked with the Formula and I understood the explanation you gave me, took a while fr my head. But, understood completely about the Calculation part perfecto.! 

 

Thanks a ton..!

 

Olympus has been Saved..!

 

 
DataRecoveryNederlandDataRecoveryNederland

hey,

 

good to know that its working happy to help.

im new with salesforce to but a student that can help other students is always nice.

 
T-HanT-Han

DataRecoveryNederland

 

Hi Again.

 

Now I am getting lil deeper. Hope to some help.

 

F1 = Date Field without weekends.

F2 = Custom Formula Field (number)

F3 = Date Field

 

I need to calculate:

 

F1 = F2+F3, but I should not get dates with Weekends.

 

This is my Previous code with standard number of days, this time its variable number based on a field.

 

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

 

Need assistance in this please.

 
DataRecoveryNederlandDataRecoveryNederland

the variable is something im really struggling with myself.

its not that easy and not really variable but here is what i do.

 

i got a start date. a drop down menu (F2) with the variable days you need. in this case 1.7,10 but you can add if you understand the formula.

the only problem here is the 5000 limit.

 

 

CASE(F2,

 

"1",
CASE(MOD((F3) - DATE(1900, 1, 7) , 7),
5, F3+3,
6, F3+2,
F3+1),

 

"7",
CASE(MOD((F3) - DATE(1900, 1, 7) , 7),
5, F3+9,
6, F3+8,
F3+7),

 

"10",F3+14),

null)

 

 

just wrote this without help so stick with me if it got errors doing my best :P

 
T-HanT-Han

Is this Formula for days from 1 to 10 or 1,7, and 10?

 

And regarding the limit, I guess the only option is to write code fr it. But, I am not into coding at all. So have no idea how to start it. Let me know your thoughts/.
DataRecoveryNederlandDataRecoveryNederland

its for 1,7,10   so a drop down menu with those 3 options and it takes + so many working days.

but you can add/change the days as you like. just not to many
David Roberts 4David Roberts 4
I needed this in Apex and came up with:
  
@RemoteAction
    public static Date calcEndDate(Date startdt, Integer workdays) {
//see https://developer.salesforce.com/forums?id=906F00000008xD9IAI
        /*
        The intent of the formula is to add a number of "working days" to a start date so that weekends are skipped over.
		To do this, it figures out how many workdays are in the first week -- which may be a partial week.
		Then some number of full weeks in the middle.
		Then how many days are in the final partial week.
		Then adds in the number of "weekend days".
		*/
        integer startday;
        integer firstweek = 0;
        integer weeks = 0;
        integer afterfirstweek = 0;
        integer lastweek = 0;
        date refdt = date.newinstance(1999, 11, 1); //mon = 1
        
        //system.debug('days between = '+refdt.daysBetween(startdt));
        startday = Math.mod((refdt.daysBetween(startdt))+1 , 7);
        //system.debug('startday = '+startday);
        firstweek=6-startday; //workdays
        if (firstweek < 0) {firstweek = 0;}
        //ignore first weekend here - add later
        //system.debug('firstweek = '+firstweek);
    
    
        //if startday + working days > 5 then more weeks
        if (startday + workdays > 5){
            afterfirstweek = workdays - firstweek;
            //system.debug('afterfirstweek = '+afterfirstweek);
            weeks = math.floor(afterfirstweek / 5).intValue();
            system.debug('weeks = '+weeks);
            lastweek = math.mod(afterfirstweek,5);
            //system.debug('lastweek = '+lastweek);
            if (weeks>0 || lastweek>0) {
                firstweek = firstweek + 2; //add first weekend
            }
        }
        
        integer duration = firstweek + weeks*7 + lastweek;
       // system.debug('duration = '+duration);
        Date endDate = startdt.addDays(duration);


    	return endDate;
 
    }//calcEndDate

 
David Roberts 4David Roberts 4
And here's a javascript version... (reverse of https://community.dynamics.com/crm/f/117/t/264052)
  
function calcEndDate(startDate,workdays){
            var count = 0;
            var curDate = new Date(startDate.valueOf());
            curDate.setDate(curDate.getDate() - 1);
            while (count < workdays) {
                curDate.setDate(curDate.getDate() + 1);
                var dayOfWeek = curDate.getDay();
                if(!((dayOfWeek == 6) || (dayOfWeek == 0)))
                    count++;
            }
            return curDate;
           
        }//calcEndDate