function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Joris Guillet 16Joris Guillet 16 

Calculate the number of days between two dates while excluding Sundays

Hi,

I am trying to create a formula fied which return number of days between two dates while excluding ONLY sundays.

I am trying with the formula below as base :
CASE(MOD( Date_de_reception__c - DATE(1985,6,24),7),
 
  0 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
  1 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
  2 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
  3 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
  4 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
  5 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
  6 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
  999)
  +
  (FLOOR(( Date_de_prise_en_charge__c - Date_de_reception__c )/7)*5)

This formula return the number of weekdays. I only miss obly saturday. I assume my error is on (MOD) operator.

Would you help me to solve this formula and explain to me ?

Thanks
Best Answer chosen by Joris Guillet 16
Alain CabonAlain Cabon

1) Calculate the number of days between two dates while excluding weekends or weekdays.​

https://help.salesforce.com/articleView?id=000004526&type=1

2)   (FLOOR(( Date_de_prise_en_charge__c - Date_de_reception__c )/7)*5)

Date_de_prise_en_charge__c - Date_de_reception__c : number of days between the two dates.
( FLOOR(( Date_de_prise_en_charge__c - Date_de_reception__c ) /7 ) : number of entire weeks between the two dates ( 7 days per week ).
Number of weeks between the two dates. x 5 = number of days between the dates with 5 days per week for complete weeks.

The new rule for excluding only Sunday:

 ( FLOOR(( Date_de_prise_en_charge__c - Date_de_reception__c ) /7 ) * 6 ) = number of days between the dates for complete weeks with 6 days per week.

3)  The number of days for the incomplete weeks before and after the complete weeks :  24/6/1985 is Monday.

MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7) = remaining days of the incomplete weeks.

CASE(MOD( Date_de_reception__c - DATE(1985,6,24),7),    
  0 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),   // Date_de_reception__c is Monday
  1 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),   // Tuesday
  2 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
  3 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
  4 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
  5 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
  6 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
  999)    // impossible but needed


0 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2, 2,3, 3,4, 4,5, 5,5, 6,5, 1), 

Date_de_reception__c = Monday + 1 remaining days = Tuesday = +2 days
Date_de_reception__c = Monday + 2 remaining days = Wednesday = +3 days
Date_de_reception__c = Monday + 3 remaining days = Thursday = +4 days
Date_de_reception__c = Monday + 4 remaining days = Friday = +5 days 
Date_de_reception__c = Monday + 5 remaining days = Saturday = +5 days (like Friday)
Date_de_reception__c = Monday + 6 remaining days = Sunday = +5 days (like Friday)
Date_de_reception__c = Monday + 0 remaining days = Monday = +1 day

The new rule for excluding just Sunday :

Date_de_reception__c = Monday + 1 remaining days = Tuesday = +2 days
Date_de_reception__c = Monday + 2 remaining days = Wednesday = +3 days
Date_de_reception__c = Monday + 3 remaining days = Thursday = +4 days
Date_de_reception__c = Monday + 4 remaining days = Friday = +5 days 
Date_de_reception__c = Monday + 5 remaining days = Saturday = +6 days
Date_de_reception__c = Monday + 6 remaining days = Sunday = +6 days (like Saturday)
Date_de_reception__c = Monday + 0 remaining days = Monday = +1 day

CASE(MOD( Date_de_reception__c - DATE(1985,6,24),7),    // remaining days
  0 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7), 1 (remaining days),2,  2,3,  3,4,  4,5,  5,6, 6,6, 1),   //  Monday

and so on.

All Answers

Alain CabonAlain Cabon

1) Calculate the number of days between two dates while excluding weekends or weekdays.​

https://help.salesforce.com/articleView?id=000004526&type=1

2)   (FLOOR(( Date_de_prise_en_charge__c - Date_de_reception__c )/7)*5)

Date_de_prise_en_charge__c - Date_de_reception__c : number of days between the two dates.
( FLOOR(( Date_de_prise_en_charge__c - Date_de_reception__c ) /7 ) : number of entire weeks between the two dates ( 7 days per week ).
Number of weeks between the two dates. x 5 = number of days between the dates with 5 days per week for complete weeks.

The new rule for excluding only Sunday:

 ( FLOOR(( Date_de_prise_en_charge__c - Date_de_reception__c ) /7 ) * 6 ) = number of days between the dates for complete weeks with 6 days per week.

3)  The number of days for the incomplete weeks before and after the complete weeks :  24/6/1985 is Monday.

MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7) = remaining days of the incomplete weeks.

CASE(MOD( Date_de_reception__c - DATE(1985,6,24),7),    
  0 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),   // Date_de_reception__c is Monday
  1 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),   // Tuesday
  2 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
  3 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
  4 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
  5 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
  6 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
  999)    // impossible but needed


0 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7),1,2, 2,3, 3,4, 4,5, 5,5, 6,5, 1), 

Date_de_reception__c = Monday + 1 remaining days = Tuesday = +2 days
Date_de_reception__c = Monday + 2 remaining days = Wednesday = +3 days
Date_de_reception__c = Monday + 3 remaining days = Thursday = +4 days
Date_de_reception__c = Monday + 4 remaining days = Friday = +5 days 
Date_de_reception__c = Monday + 5 remaining days = Saturday = +5 days (like Friday)
Date_de_reception__c = Monday + 6 remaining days = Sunday = +5 days (like Friday)
Date_de_reception__c = Monday + 0 remaining days = Monday = +1 day

The new rule for excluding just Sunday :

Date_de_reception__c = Monday + 1 remaining days = Tuesday = +2 days
Date_de_reception__c = Monday + 2 remaining days = Wednesday = +3 days
Date_de_reception__c = Monday + 3 remaining days = Thursday = +4 days
Date_de_reception__c = Monday + 4 remaining days = Friday = +5 days 
Date_de_reception__c = Monday + 5 remaining days = Saturday = +6 days
Date_de_reception__c = Monday + 6 remaining days = Sunday = +6 days (like Saturday)
Date_de_reception__c = Monday + 0 remaining days = Monday = +1 day

CASE(MOD( Date_de_reception__c - DATE(1985,6,24),7),    // remaining days
  0 , CASE( MOD( Date_de_prise_en_charge__c - Date_de_reception__c ,7), 1 (remaining days),2,  2,3,  3,4,  4,5,  5,6, 6,6, 1),   //  Monday

and so on.
This was selected as the best answer
Vinod ChoudharyVinod Choudhary
Hi Joris,

Steps to follow:

Create a formula field that returns a number.
Paste in one of the two formulas.
Replace Date_de_prise_en_charge__c  and Date_de_reception__c  with your custom field values.
If using Date/Time fields
Replace with DATEVALUE(YourCustomDateTime__c) instead.
Assumptions/Limitations/Background:
Weekdays are defined as Mon-Fri and weekends as Sat & Sun
Holidays are not addressed

The result will include BOTH the START and END dates.
Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.
Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.
Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.
If you use another Formula field as the start or end dates you MAY hit a compilation limit.
Workaround - Use workflow rules to save the output of the formula fields into a regular date field.
  
CASE(MOD( Date_de_prise_en_charge__c  - DATE(1985,6,24),7), 
  0 , CASE( MOD( Date_de_reception__c  - Date_de_prise_en_charge__c  ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( Date_de_reception__c  - Date_de_prise_en_charge__c  ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( Date_de_reception__c  - Date_de_prise_en_charge__c  ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( Date_de_reception__c  - Date_de_prise_en_charge__c  ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( Date_de_reception__c  - Date_de_prise_en_charge__c  ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( Date_de_reception__c  - Date_de_prise_en_charge__c  ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( Date_de_reception__c  - Date_de_prise_en_charge__c  ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( Date_de_reception__c  - Date_de_prise_en_charge__c  )/7)*5)

Hope this will help you.

Thanks
Vinod

 
Joris Guillet 16Joris Guillet 16
Hi,

Thanks Alain and Vinod. It works fine :)

Here is the succesfull formula :

CASE(MOD( Date_de_reception__c - DATE(1985,6,24),7), 

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