You need to sign in to do that
Don't have an account?

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
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
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
BusinessHours
diff(businessHoursId, startDate, endDate)
Returns the difference between a start and end Datetime based on a specific set of business hours.
have you tried this solution?
https://help.salesforce.com/HTViewSolution?id=000004526
((Datevalue(date2__c) -Datevalue(date1__c))*5)/7
Will not take time into consideration, but will return the # of business days with fractions
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.
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)
Could you please let me know the fraction for (GMT+05:30) India Standard Time (Asia/Kolkata)
Thanks in advance
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)))
)
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
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.
(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
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