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
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.
===========================================================================
Regards,
Santhi.
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.
Please select UK time zone from business hours section.
Kindly mark my solution as the best answer if it helps you.
Thanks
Mukesh
Kindly mark my solution as the best answer if it helps you.
Regards
Mukesh