I want to calculate the total number of working hours (Mon-Fri 8.30am-5pm) between two date/time fields. For this purpose I created the following two formula fields:
- TT_Calculate_Emails_SLA__c (Formula Number - length 4, decimal 2)
ROUND(
8.5 * (
( 5 *
FLOOR(
( DATEVALUE(
IF(
TEXT(Status__c) = "Closed",
Closed__c,
NOW()
)
) - DATE(1900, 1, 8)
) / 7
)
+ MIN(
5,
MOD(
DATEVALUE(
IF(
TEXT(Status__c) = "Closed",
Closed__c,
NOW()
)
) - DATE(1900, 1, 8),
7
)
+ MIN(
1,
24 / 8.5 * (
MOD(
IF(
TEXT(Status__c) = "Closed",
Closed__c,
NOW()
) - DATETIMEVALUE('1900-01-08 08:30:00'),
1
)
)
)
)
)
- ( 5 * FLOOR(
(
DATEVALUE(TT_Case_Re_Open_Time__c) - DATE(1900, 1, 8)
) / 7)
+ MIN(
5,
MOD(
DATEVALUE(TT_Case_Re_Open_Time__c) - DATE( 1996, 1, 1),
7
)
+ MIN(
1,
24 / 8.5 * (
MOD(
TT_Case_Re_Open_Time__c - DATETIMEVALUE('1900-01-08 08:30:00'),
1
)
)
)
)
)
),
2
)
The above formula fields rounds up the number and does not display the actual value. For example, is displays 2.61 instead of 2.37 (2 hours, 37 min) and 0.69 instead of 0.41 (0 hours, 41 min)
To solve the issue with the above formula, I created another formula field to show the actual value.
- TT_Emails_SLA__c (Formula Number - length 4, decimal 2).
VALUE(
TEXT(
MOD(FLOOR( TT_Calculate_Emails_SLA__c ), 24 )
) & "." &
TEXT(
ROUND(MOD(TT_Calculate_Emails_SLA__c, 1) * 60 , 0 )
)
)
The second formula field works fine for number greater than 10, but for any number between 1 to 9 it adds an extra zero after the number.
For example, for 1 hour, 03 min, it shows 1.30 (the user will think it is 1 hour: 30 min).
Similarly, for 0 hour, 05 min, it shows (0.50). This looks like 50 min and not 5 min.
ROUND(MOD(TT_Calculate_Emails_SLA__c, 1) * 60 , 0 )
) is incorrect/incomplete.