Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
raji devi 1raji devi 1 

Date formal to add 15 working days to created date

Hi All,
I have a senariou like this, i have  Created Date field(custom field)  and   Expire Date __c (Formal Datefield). For Example Created date is 6/09/2016 then the Expiredate__c should add 15 working days(30/06/2016) (exclude sunday and saturday) to CreatedDate__C.
My logic for this is :
 CASE( MOD( date - DATE( CreatedDate__C ), 7 ),
3, date + 2 + 15,
4, date + 2 + 15,
5, date + 2 + 15,
6, date + 1 + 15,
   date + 15
)
But it is not working proparly. Any one please help me to over come this.

Thanks and Regards,
Raji
Best Answer chosen by raji devi 1
mritzimritzi
IF( MOD (CreatedDate__c - Date(1970,01,05) , 7) <=4 , Date__c+15+6,
   IF(MOD (CreatedDate__c - Date(1970,01,05) , 7) = 5, Date__c+15+5,Date__c+15+4
   )
)


Note: Jan 05 1970 is Monday, so mod operation will give values from 0-6. 0 for monday, 6 for sunday

If this solves your problem, please mark it as Best Answer.

All Answers

raji devi 1raji devi 1
Thanks for the reply. i changed the logic like this still not working : CASE( MOD( CreatedDate__c - DATE( 1900, 1, 7 ), 7 ), 3, CreatedDate__c + 2 + 15, 4, CreatedDate__c + 2 + 15, 5, CreatedDate__c + 2 + 15, 6, CreatedDate__c + 1 + 15, CreatedDate__c + 15 )
mritzimritzi
IF( MOD (CreatedDate__c - Date(1970,01,05) , 7) <=4 , Date__c+15+6,
   IF(MOD (CreatedDate__c - Date(1970,01,05) , 7) = 5, Date__c+15+5,Date__c+15+4
   )
)


Note: Jan 05 1970 is Monday, so mod operation will give values from 0-6. 0 for monday, 6 for sunday

If this solves your problem, please mark it as Best Answer.
This was selected as the best answer
raji devi 1raji devi 1
Hi, 
Thanks  alot it is working what we expect , can you explain little bit.

Thanks,
Raji
mritzimritzi
Standard formula syntax is :
IF(logical_test, value_if_true, value_if_false) 

Here logical_test -> MOD (CreatedDate__c - Date(1970,01,05) , 7) <=4
value_if_true -> Date__c+15+6
Value_if_false -> IF(MOD (CreatedDate__c - Date(1970,01,05) , 7) = 5, Date__c+15+5,Date__c+15+4 )

Instead of stating a value for value_if_false part, an IF statement has been used to acheive functionality of nested IF.

The first MOD (CreatedDate__c - Date(1970,01,05) , 7) <=4 checks whether Day of Created Date__c is from monday to friday,
if this evaluates to true then formula returns CteatedDate__c +15 working days + 6 days (for 3 weekends)
if this evalautes to false
then two cases arise, either the Day of CreatedDate__c is saturday or sundays

The second MOD (CreatedDate__c - Date(1970,01,05) , 7) =5 checks whether the Day is Saturday
if it evaluates to true then formula returns CteatedDate__c +15 working days + 5 days (next sunday and two more weekends)
else it returns CteatedDate__c +15 working days + 4 days (next two weekends)

Hope it helps.
raji devi 1raji devi 1
Hi, Thanks really it helpful. Thanks, Raji
Tori Sansom 9Tori Sansom 9
Hi 

I just used this formula and it worked perfectly for me!!! Thank you!! I need a second field that then caculates this date minus - 2 working days! 

Can you please help me with this formula? You need to be mindful that the working week here in Dubai is Sun-Fri 

Many thanks 
mritzimritzi
@Torl
This thread is marked SOLVED.

please start a new thread by asking a question and people will help you.