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

July 10, 2015
·
Like
0
·
Follow
7

Best Answer chosen by Aravind R

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

)

July 15, 2015
·
Like
0
·
Dislike
0

Jason 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;

July 10, 2015
·
Like
1
·
Dislike
0

Art Smorodin
Hi Aravind,

have you tried this solution?

https://help.salesforce.com/HTViewSolution?id=000004526

July 10, 2015
·
Like
0
·
Dislike
0

cldave
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

July 10, 2015
·
Like
0
·
Dislike
0

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

July 12, 2015
·
Like
0
·
Dislike
0

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

July 13, 2015
·
Like
0
·
Dislike
0

Aravind R
Hi Jason,

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

Thanks in advance

July 13, 2015
·
Like
0
·
Dislike
0

Jason Beal
Replace the 5 instances of "- (4/24)" with "+ (5.5/24)" and replace "+ (4/24)" with "- (5.5/24)"

July 14, 2015
·
Like
1
·
Dislike
0

Jason Beal
How did you make out Aravind? Is that forumla giving the expected result?

July 15, 2015
·
Like
0
·
Dislike
0

This was selected as the best answer

jas 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

November 10, 2015
·
Like
0
·
Dislike
0

Korhan Mulcar
Hi Jason, I tried to change the GMT as you described but it doesn't work, it gives the same value.

July 21, 2016
·
Like
0
·
Dislike
0

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

October 13, 2016
·
Like
0
·
Dislike
1

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

October 13, 2016
·
Like
0
·
Dislike
0

Sarada RVS 2
Did any get this working?

July 25, 2017
·
Like
0
·
Dislike
0

Phil 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

January 17, 2019
·
Like
0
·
Dislike
0

Phil 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

January 17, 2019
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

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

All Answers

You need to sign in to do that.