You need to sign in to do that
Don't have an account?
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.
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)
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.
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
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\
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.?
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
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.!
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
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..!
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.
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.
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
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/.
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