Salesforce: Calculate age for weekdays and weekends

In Salesforce, it is easy to calculate the number of days between 2 dates. Just create a formula field, for example: End_Date__c - Start_Date__c for date fields, or  Datevalue(End_Date__c) - Datevalue(Start_Date__c) for datetime fields and DONE!!!

But, is it possible to find out the number of days - only weekdays and only weekends between 2 dates?
Hmmm.... most of us will think about Apex Trigger. Yes, it is the correct solution, apex trigger is able to calculate without issue, but if you are not a developer, you need a developer for this.

Wait a minute.... Can we 'just' use a formula field to calculate weekdays and weekends?

YES, it is possible with complex formulas. Here we go:

To calculate the number of  Weekday

IF ((CASE(MOD( Request_Date__c - DATE(1900,1,1),7),

0, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),

1, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),

2, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),

3, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),

4, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),

5, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),

6, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),

999)

+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*5)

- 1) < 0,

0,

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

0, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),

1, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),

2, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),

3, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),

4, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),

5, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),

6, CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),

999)

+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*5)

- 1)

To calculate number of  Weekend

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

0, CASE( MOD( Execution_Date__c - Request_Date__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,1,2),
3, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,1,2),
4, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,1,2),
5, CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,1,2),
6, CASE( MOD( Execution_Date__c - Request_Date__c, 7),6,2,1),
999)
+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*2)

Example:
Request Date = 1 Jan 2014
Execution Date = 10 Jan 2014
Total days on weekdays = 7
Total days on the weekend = 2

Request Date = 1 Mar 2014
Execution Date = 10 Mar 2014
Total days on weekdays = 5
Total days on the weekend = 4

If you see in the formula above, we have DATE(1900,1,1); this is referred to as 1-Jan-1900 is Monday. So, you can use any date which is Monday, for example, 1-Jan-2007

The rest is just the MOD() function, so have fun...


Another request is to calculate a date after X days from today (business day means weekday from Mon - Fri). Here is the formula; the result will be a formula in Date format, while Days__c is a number.

CASE( 
MOD(TODAY()- DATE(1900,1,7),7), 
0, (TODAY()) + Days__c+ FLOOR((Days__c-1)/5)*2, 
1, (TODAY()) + Days__c+ FLOOR((Days__c)/5)*2, 
2, (TODAY()) + Days__c+ FLOOR((Days__c+1)/5)*2, 
3, (TODAY()) + Days__c+ FLOOR((Days__c+2)/5)*2, 
4, (TODAY()) + Days__c+ FLOOR((Days__c+3)/5)*2, 
5, (TODAY()) + Days__c+ CEILING((Days__c)/5)*2, 
6, (TODAY()) - IF(Days__c > 0,1,0) + Days__c + CEILING((Days__c)/5)*2, NULL)

Result sample: today is 3 Oct 2016, and Days are 5, so the result would be 10 Oct 2016.