ShowAll Questionssorted byDate Posted
cased19

# 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!

cased19

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)
)

hhuie

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,

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

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

cased19

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..

hhuie

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)
)

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

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