You need to sign in to do that
Don't have an account?
santhi jeevana
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.
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 )
Make sure that you have selected UK time zone from business hours section.
Best regards,
Sandhya