Sign Up ›
  • santhi jeevana
  • NEWBIE
  • 0 Points
  • Member since 2018
  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 2
    Questions
  • 0
    Replies
1 replies

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

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(StartDate__C - (5/24)), 10) = LEFT(TEXT( EndDate__C - (5/24)), 10), 
IF( 
AND( 
StartDate__C > DATETIMEVALUE(LEFT(TEXT( StartDate__C - (5/24)),11) & "22:00:00"), 
EndDate__C > DATETIMEVALUE(LEFT(TEXT( EndDate__C - (5/24)),11) & "22:00:00") 
), 0, 
MAX( 
MIN((EndDate__C - DATETIMEVALUE(LEFT(TEXT( EndDate__C - (5/24)),11) & "05:00:00")) *24 , 17) 

- 

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


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

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


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

 
2 replies

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.