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
santhi jeevanasanthi jeevana 

formula field to calculate the number of business hours between two date/time fields

The below formula to calculate the number business hours between two date/time fields.
currently, it is working for  EST time only, but it is not working for UK time zone.
Working hours are 8 AM to 5 PM
what changes are to be done for the formula field that should be work for Uk time zone.
===========================================================================
IF(LEFT(TEXT(CreatedDate - (5/24)), 10) = LEFT(TEXT( Intake_Complete__c - (5/24)), 10), 
IF( 
AND( 
CreatedDate > DATETIMEVALUE(LEFT(TEXT( CreatedDate - (5/24)),11) & "22:00:00"), 
Intake_Complete__c > DATETIMEVALUE(LEFT(TEXT( Intake_Complete__c - (5/24)),11) & "22:00:00") 
), 0, 
MAX( 
MIN((Intake_Complete__c - DATETIMEVALUE(LEFT(TEXT( Intake_Complete__c - (5/24)),11) & "05:00:00")) *24 , 17) 

- 

MAX((CreatedDate - DATETIMEVALUE(LEFT(TEXT( CreatedDate - (5/24)),11) & "05:00:00"))*24, 8) , 0) 
) 
, 
MAX(((CASE(MOD(DATEVALUE(CreatedDate) - DATE(1900,1,8),7), 
0 , CASE( MOD(DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD(DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD(DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD(DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD(DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD(DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD(DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ (FLOOR((DATEVALUE(Intake_Complete__c) - DATEVALUE(CreatedDate))/7)*5) 
- 2) * 9) , 0) 
+ 


IF( (DATEVALUE(CreatedDate) + CASE(MOD(DATEVALUE(CreatedDate) - DATE(1900,1,7), 7), 6, 2, 0, 1, 0)) = DATEVALUE(Intake_Complete__c), 0, 

MAX(17 - MAX((CreatedDate - DATETIMEVALUE(LEFT(TEXT( CreatedDate - (5/24)),11) & "05:00:00"))*24,8), 0)) 


+ 
IF(OR(MOD(DATEVALUE(Intake_Complete__c ) - DATE(1900,1,7), 7) = 6, MOD(DATEVALUE(Intake_Complete__c ) - DATE(1900,1,7), 7) = 0), 0, 
IF( Intake_Complete__c <= DATETIMEVALUE(LEFT(TEXT( Intake_Complete__c - (5/24)),11) & "13:00:00"), 0, 
IF( Intake_Complete__c >= DATETIMEVALUE(LEFT(TEXT( Intake_Complete__c - (5/24)),11) & "22:00:00"), 9/24, 
Intake_Complete__c - DATETIMEVALUE(LEFT(TEXT( Intake_Complete__c - (5/24)),11) & "13:00:00")))) 
*24 )


Regards,
Santhi.


 
mukesh guptamukesh gupta
Hi Santhi,

Please select UK time zone from business hours section.

User-added image


Kindly mark my solution as the best answer if it helps you.


Thanks
Mukesh
mukesh guptamukesh gupta
Hi Santhi,

Kindly mark my solution as the best answer if it helps you.

Regards
Mukesh