0

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:

  1. 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.

  1. 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.

1
  • 1
    An answer I gave back in 2015 should give some helpful insight. Tl;dr: your conversion between the decimal domain (2.61) and the time domain (2:37) (i.e. ROUND(MOD(TT_Calculate_Emails_SLA__c, 1) * 60 , 0 )) is incorrect/incomplete.
    – Derek F
    Mar 21 at 12:57

1 Answer 1

0

The decimal portion of the number is the fraction of 60 minutes. Therefore, 0.61 * 60 = 37 minutes. You must include this conversion factor in your formula.

1
  • thanks for the response. Which formula should I update? formula field 1 or 2? can you please share the updated formula if possible
    – Marjan
    Mar 26 at 11:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .