function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Aravind RAravind R 

Calculate working days between two date/time field excluding weekends with fraction value

I have a twe date/time fields: Date1__c and Date2__c.

I need to calculate working days between two date/time field excluding weekends with fraction value.

Eg., Date1__c = '2015 - 07 - 08 13:00'
        Date2__c = '2015 - 07 - 10 15:00'
        difference: 2.08
Best Answer chosen by Aravind R
Aravind RAravind R
Thanks Jason for your valuable input.

I had tried in a different way which is working fine as expected. Here is the formula:

IF( ISBLANK(Solution_Request_Closed_Date_Time__c ), 
(CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 
0 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 

(FLOOR(( TODAY() - DATEVALUE(CreatedDate))/7)*5))-1+NOW() - CreatedDate-(TODAY() - DATEVALUE(CreatedDate))
,
 
(CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 
0 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 

(FLOOR(( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate))/7)*5)
)-1+(Solution_Request_Closed_Date_Time__c - CreatedDate-(DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate))) 

)

All Answers

Jason BealJason Beal
You can use the BusinessHours objects diff method for this. Define yourself a set of business hours (Setup > Company Profile > Business Hours) remove the hours from saturday and sunday. I named this set of BusinessHours "weekdays" in my example below. 

BusinessHours
diff(businessHoursId, startDate, endDate)
Returns the difference between a start and end Datetime based on a specific set of business hours.
 
Id businessHoursId = [select Id from BusinessHours where name = 'weekdays' limit 1][0].id;
DateTime dateTime1 = Datetime.valueOf('2015-07-10 12:00:00');
DateTime dateTime2 = Datetime.valueOf('2015-07-11 12:00:00');
Long hours = Math.abs(BusinessHours.diff(businessHoursId,dateTime1,dateTime2)/1000/60/60);
Decimal days = hours / 24.0;

 
Art SmorodinArt Smorodin
Hi Aravind, 

have you tried this solution? 

https://help.salesforce.com/HTViewSolution?id=000004526
 
cldavecldave
Will not be perfect but you maybe use this simple formuala

((Datevalue(date2__c) -Datevalue(date1__c))*5)/7

Will not take time into consideration, but will return the # of business days with fractions
Aravind RAravind R
Hi Jason,
I need it in formula field. It should be real time.

Hi Art.
I have tried that solution. But it is giving without fraction. I want it in fraction.
Jason BealJason Beal
The formula below will get you what you need but it must have the timezone hard coded. (4/24) for EDT UTC-4:00, replace this with an appropriate fraction for your time zone.

This solution may not be ideal for you since it does not factor in daylight savings time (it could with more effort) or if your users are in multiple time zones. It may be better to create a trigger and use the apex code I posted earlier. For your reference I've included a couple of links that document issues when working with time in formulas. 

Overview of Using Date and Date/Time Values in Formulas​
https://help.salesforce.com/HTViewHelpDoc?id=formula_using_date_datetime.htm&language=en_US#timezone​ (https://help.salesforce.com/HTViewHelpDoc?id=formula_using_date_datetime.htm&language=en_US#timezone)
https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#getting-hms-from-datetime​ (https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#getting-hms-from-datetime​)
(FLOOR((DATEVALUE(EndDate__c) - DATEVALUE(StartDate__c))/7)*5)
+ CASE(
   MOD(DATEVALUE(StartDate__c) - DATE(1900,1,1),7), 
      0 , CASE( MOD( FLOOR(EndDate__c - StartDate__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,7,1,0), 
      1 , CASE( MOD( FLOOR(EndDate__c - StartDate__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,7,1,0), 
      2 , CASE( MOD( FLOOR(EndDate__c - StartDate__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,7,1,0), 
      3 , CASE( MOD( FLOOR(EndDate__c - StartDate__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,7,1,0), 
      4 , CASE( MOD( FLOOR(EndDate__c - StartDate__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,7,1,0), 
      5 , CASE( MOD( FLOOR(EndDate__c - StartDate__c) ,7),1,0,2,1,3,2,4,3,5,4,6,5,7,0,0), 
      6 , CASE( MOD( FLOOR(EndDate__c - StartDate__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,7,0,0),
      0)
+ CASE(
   MOD(DATEVALUE(StartDate__c) - DATE(1900,1,1),7), 
      0 , 1 - (StartDate__c - datetimevalue(datevalue(StartDate__c))) + (4/24),
      1 , 1 - (StartDate__c - datetimevalue(datevalue(StartDate__c))) + (4/24),
      2 , 1 - (StartDate__c - datetimevalue(datevalue(StartDate__c))) + (4/24),
      3 , 1 - (StartDate__c - datetimevalue(datevalue(StartDate__c))) + (4/24),
      4 , 1 - (StartDate__c - datetimevalue(datevalue(StartDate__c))) + (4/24),
      5 , 0,
      6 , 0,
      0)
+ CASE(
   MOD(DATEVALUE(EndDate__c) - DATE(1900,1,1),7), 
      0 , EndDate__c - datetimevalue(datevalue(EndDate__c)) - (4/24),
      1 , EndDate__c - datetimevalue(datevalue(EndDate__c)) - (4/24),
      2 , EndDate__c - datetimevalue(datevalue(EndDate__c)) - (4/24),
      3 , EndDate__c - datetimevalue(datevalue(EndDate__c)) - (4/24),
      4 , EndDate__c - datetimevalue(datevalue(EndDate__c)) - (4/24),
      5 , 0,
      6 , 0,
      0)

 
Aravind RAravind R
Hi Jason,

Could you please let me know the fraction for (GMT+05:30) India Standard Time (Asia/Kolkata)

Thanks in advance
Jason BealJason Beal
Replace the 5 instances of "- (4/24)" with "+ (5.5/24)" and replace "+ (4/24)" with "- (5.5/24)"
Jason BealJason Beal
How did you make out Aravind? Is that forumla giving the expected result?
Aravind RAravind R
Thanks Jason for your valuable input.

I had tried in a different way which is working fine as expected. Here is the formula:

IF( ISBLANK(Solution_Request_Closed_Date_Time__c ), 
(CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 
0 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( TODAY() - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 

(FLOOR(( TODAY() - DATEVALUE(CreatedDate))/7)*5))-1+NOW() - CreatedDate-(TODAY() - DATEVALUE(CreatedDate))
,
 
(CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 
0 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 

(FLOOR(( DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate))/7)*5)
)-1+(Solution_Request_Closed_Date_Time__c - CreatedDate-(DATEVALUE( Solution_Request_Closed_Date_Time__c ) - DATEVALUE(CreatedDate))) 

)
This was selected as the best answer
jas pjas p

Dear @Jason Beal,

would like to ask a question (anyone who can answer this; your great help is very appreciated) 

                      " 4 Long hours = Math.abs(BusinessHours.diff(businessHoursId,dateTime1,dateTime2)/1000/60/60);"

& why do we divide by "1000" for? and why divide by "60" 2 times?

I couldn't really understand, would appreciate your great help T.T 

 

2nd question, if i want to calculate minute as well; which should i remove? the 1000 or 60?



Thank you very much for any help offered T.T
 

Korhan MulcarKorhan Mulcar
Hi Jason, I tried to change the GMT as you described but it doesn't work, it gives the same value.
Mohan Raj 33Mohan Raj 33
@Jason Beal , hi I have the same problem to be try to calculated  the two date/time values and store the result in number field I saw in your code in now Could you tell me to here in your code what is the static and dynamic field in the start date and end date  because I have the two date/time field as mentioned above in Calculating _ Date_c and the another NOW() . So here the NOW() is to be dynamic so that's why I am asking this question because I want help to solve my problem. 
Mohan Raj 33Mohan Raj 33
@Jason Beal , I try to your code  with in small changes as like follow But It's not also provide the correct result Can you send me if any wrong in making changes on your code. My formula is followingly, 
FLOOR((DATEVALUE(NOW()) - DATEVALUE(Calculating_Date__c)))
+ CASE(
   MOD(DATEVALUE(Calculating_Date__c) - DATE(1900,1,1),7), 
      0 , CASE( MOD( FLOOR(NOW() - Calculating_Date__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,7,1,0), 
      1 , CASE( MOD( FLOOR(NOW() - Calculating_Date__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,7,1,0), 
      2 , CASE( MOD( FLOOR(NOW() - Calculating_Date__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,7,1,0), 
      3 , CASE( MOD( FLOOR(NOW() - Calculating_Date__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,7,1,0), 
      4 , CASE( MOD( FLOOR(NOW() - Calculating_Date__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,7,1,0), 
      5 , CASE( MOD( FLOOR(NOW() - Calculating_Date__c) ,7),1,0,2,1,3,2,4,3,5,4,6,5,7,0,0), 
      6 , CASE( MOD( FLOOR(NOW() - Calculating_Date__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,7,0,0),
      0)
+ CASE(
   MOD(DATEVALUE(Calculating_Date__c) - DATE(1900,1,1),7), 
      0 , 1 - (Calculating_Date__c - datetimevalue(datevalue(Calculating_Date__c))) - (5.5/24),
      1 , 1 - (Calculating_Date__c - datetimevalue(datevalue(Calculating_Date__c))) - (5.5/24),
      2 , 1 - (Calculating_Date__c - datetimevalue(datevalue(Calculating_Date__c))) - (5.5/24),
      3 , 1 - (Calculating_Date__c - datetimevalue(datevalue(Calculating_Date__c))) - (5.5/24),
      4 , 1 - (Calculating_Date__c - datetimevalue(datevalue(Calculating_Date__c))) - (5.5/24),
      5 , 1 - (Calculating_Date__c - datetimevalue(datevalue(Calculating_Date__c))) - (5.5/24),
      6 , 1 - (Calculating_Date__c - datetimevalue(datevalue(Calculating_Date__c))) - (5.5/24),
      0)
+ CASE(
   MOD(DATEVALUE(NOW()) - DATE(1900,1,1),7), 
      0 , NOW() - datetimevalue(datevalue(NOW())) + (5.5/24),
      1 , NOW() - datetimevalue(datevalue(NOW())) + (5.5/24),
      2 , NOW() - datetimevalue(datevalue(NOW())) + (5.5/24),
      3 , NOW() - datetimevalue(datevalue(NOW())) + (5.5/24),
      4 , NOW() - datetimevalue(datevalue(NOW())) + (5.5/24),
      5 , NOW() - datetimevalue(datevalue(NOW())) + (5.5/24),
      6 , NOW() - datetimevalue(datevalue(NOW())) + (5.5/24),
      0)

So your  code provide the output in 1.00 for the Calculating_Date__c value is in 10/13/2016 6.06AM and the NOW() value is 10/13/2016 6.36PM at the moment. So it's make un comfortable to the result in here showing 1.00 hours I think but the practical difference is 12 hours and 30 minutes only.so please help me to rectify this confusion.
Sarada RVS 2Sarada RVS 2
Did any get this working?
Phil HillPhil Hill
I never manged to get anything on here working, but I took he standard formula,-1 (as it counts both the start and end date) and then calced out the difference between the two times to get the remainder (can be +ve or -ve - formula additions in bold)

(CASE(MOD( DATEVALUE(Start_date_time) - DATE(1985,6,24),7),
 0 , CASE( MOD( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
 1 , CASE( MOD( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
 2 , CASE( MOD( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
 3 , CASE( MOD( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
 4 , CASE( MOD( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
 5 , CASE( MOD( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
 6 , CASE( MOD( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
 999)
 +
 (FLOOR(( DATEVALUE(End_date_time) - DATEVALUE(Start_date_time) )/7)*5) )
-1
+(DATETIMEVALUE(DATEVALUE(Start_date_time)) - DATETIMEVALUE(Start_date_time)
+DATETIMEVALUE(End_date_time) - DATETIMEVALUE(DATEVALUE(End_date_time))


This worked for me
Phil HillPhil Hill
FYI original formula here:
https://help.salesforce.com/articleView?id=000004526&type=1

Key thing for calcualting the remainder is that (DATETIMEVALUE(DATEVALUE(Start_date_time)) returns 00:00 on Start_date_time