How to Calculate the number of hours between two date/time field?
To calculate the business hours (or hours) between two date-time fields, we can use formula fields -
To calculate the difference between two date fields -
adateField__c - bdateField__c
And this will return the difference in days
To calculate the difference between two date-time fields -
if(
aDateTimeField - bDateTimeField > 0 ,
TEXT( FLOOR( aDateTimeField - bDateTimeField ) ) & " days "&
TEXT( FLOOR( MOD( (aDateTimeField - bDateTimeField ) * 24, 24 ) ) ) & " hours "&
TEXT( ROUND( MOD( (aDateTimeField - bDateTimeField ) * 24 * 60, 60 ), 0 ) ) & " minutes",
""
)
To calculate business hours difference between two date-time fields -
ROUND(
8 * (
(
5 * FLOOR((DATEVALUE(aDateTimeField) - DATE(1900, 1, 8) ) / 7) +
MIN(
5,
MOD(DATEVALUE( aDateTimeField ) - DATE( 1900, 1, 8), 7) +
MIN(1, 24 / 8 * (MOD(aDateTimeField - DATETIMEVALUE('1900-01-08 16:00:00'),1)))
)
)
-
(
5 * FLOOR((DATEVALUE(bDateTimeField) - DATE( 1900, 1, 8) ) / 7) +
MIN(
5,
MOD(DATEVALUE(bDateTimeField) - DATE( 1996, 1, 1), 7 ) +
MIN(1, 24 / 8 * (MOD( bDateTimeField - DATETIMEVALUE('1900-01-08 16:00:00'),1)))
)
)
),
0
)
Comments
Post a Comment