Sign Up ›

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

Case Due Date Excluding Weekends & Holidays

I currently have a date/time formula that creates a due date for our case management team. However, I haven't been able to figure out how to exclude weekends and holidays (at the least weekends).

 

Could someone one offer some suggestions?

 

The current formula simply takes the createate + #

 

 

Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
cased19cased19

Thank you hhuie for pointing me in the right direction!

 

I wanted to post the formula I ended up using for our scenario.

 

Create a Case Due Date based on Case Reason and exclude weekend dates.

 

 

CASE(Reason, "CASE REASON" , Case(
MOD(datevalue(CreatedDate) - DATE(1900, 1, 7),7),
0, datevalue(CreatedDate) +5,
1, datevalue(CreatedDate) +7,
2, datevalue(CreatedDate) +7,
3, datevalue(CreatedDate) +7,
4, datevalue(CreatedDate) +7,
5, datevalue(CreatedDate) +7,
6, datevalue(CreatedDate) +6,
datevalue(CreatedDate)+5) ,
Case(
MOD(datevalue(CreatedDate) - DATE(1900, 1, 7),7),
0, datevalue(CreatedDate) +3,
1, datevalue(CreatedDate) +3,
2, datevalue(CreatedDate) +3,
3, datevalue(CreatedDate) +5,
4, datevalue(CreatedDate) +5,
5, datevalue(CreatedDate) +5,
6, datevalue(CreatedDate) +4,
datevalue(CreatedDate)+3)
)

 

 

All Answers

hhuiehhuie

Salesforce has a Validation Rule to prevent dates on Weekends

 

CASE( MOD( My_Date__c - DATE(1900, 1, 7), 7),
0, 1
6, 1,
0) = 0

 

So we modify it:

 

CASE(MOD( My_Date - DATE (1900, 1, 7), 7),

0, Your Date Formula + 1,

6, Your Date Formula + 2, Your Date Formula)

 

If the Remainder = 6 then it's on a Saturday

If the Remainder = 0 then it's on a Sunday
cased19cased19

Thank you sooo much for that.

 

Question: Can that also include another condition such as the Case Reason, Case Type or maybe both?

 

 Such as

 

CASE(Reason, "Electrical",
     MOD(datevalue(CreatedDate) - DATE(1900, 1, 7), 7)+3,
     0, datevalue(CreatedDate) +3+1,  
     6, datevalue(CreatedDate) +3+2,
     datevalue(CreatedDate)
)

 

I'm still learning the syntax... I've tried differnt variations..

 
hhuiehhuie

You can, I didn't test out this coding:

 

 CASE(Reason, "Electrical" , Case(
     MOD(datevalue(CreatedDate) - DATE(1900, 1, 7)+3, 7),
     0, datevalue(CreatedDate) +4,  
     6, datevalue(CreatedDate) +5,
     datevalue(CreatedDate)+3)
)

 

I'm assuming that your adding 3 days to the CreatedDate value

Message Edited by hhuie on 03-12-2009 09:01 AM
Message Edited by hhuie on 03-12-2009 09:02 AM
cased19cased19

Thank you hhuie for pointing me in the right direction!

 

I wanted to post the formula I ended up using for our scenario.

 

Create a Case Due Date based on Case Reason and exclude weekend dates.

 

 

CASE(Reason, "CASE REASON" , Case(
MOD(datevalue(CreatedDate) - DATE(1900, 1, 7),7),
0, datevalue(CreatedDate) +5,
1, datevalue(CreatedDate) +7,
2, datevalue(CreatedDate) +7,
3, datevalue(CreatedDate) +7,
4, datevalue(CreatedDate) +7,
5, datevalue(CreatedDate) +7,
6, datevalue(CreatedDate) +6,
datevalue(CreatedDate)+5) ,
Case(
MOD(datevalue(CreatedDate) - DATE(1900, 1, 7),7),
0, datevalue(CreatedDate) +3,
1, datevalue(CreatedDate) +3,
2, datevalue(CreatedDate) +3,
3, datevalue(CreatedDate) +5,
4, datevalue(CreatedDate) +5,
5, datevalue(CreatedDate) +5,
6, datevalue(CreatedDate) +4,
datevalue(CreatedDate)+3)
)

 

 

This was selected as the best answer
Paske11Paske11

CASE( MOD(Date_vod__c  - DATE(1900, 1, 7), 7),
0, 0,
6, 0,
1 ) = 0)

 

How can i exclude only the sunday not the saturday?! thankssss